Hi, and thanks in advance.
I am using Access 2010 and have a single table (tblData) with about 100,000 rows with an ID field (ID) and a date/time field (Tmst).
Each ID can appear one or more times with one of two dates. In the example provided, the date can be either 23/11/2015 or 1/05/2015. I want to return the ID if it has at least one instance of 23/11/2015 and no instances of 1/05/2015. I also want the IDs returned to be distinct.
From the data in the example the SQL should return: IDs: 111, 008
I tried an EXISTS subquery but it took forever to run on our slow machines so I gave up with that.
I also tried this left join SQL which ran really fast but for some reason included some IDs that have both dates, which is not what I want.
SELECT DISTINCT X.ID FROM (SELECT DISTINCT tblData.ID FROM tblData WHERE int([Tmst])=#23/11/2015#) AS X LEFT JOIN (SELECT DISTINCT tblData.ID FROM tblData WHERE int([Tmst])=#1/05/2015#) AS Y ON X.ID = Y.ID WHERE (((Y.ID) Is Null));
Regards
Deutz
I am using Access 2010 and have a single table (tblData) with about 100,000 rows with an ID field (ID) and a date/time field (Tmst).
Each ID can appear one or more times with one of two dates. In the example provided, the date can be either 23/11/2015 or 1/05/2015. I want to return the ID if it has at least one instance of 23/11/2015 and no instances of 1/05/2015. I also want the IDs returned to be distinct.
PHP:
ID Tmst
123 23/11/2015
123 1/05/2015
123 23/11/2015
111 23/11/2015
123 1/05/2015
111 23/11/2015
002 1/05/2015
111 23/11/2015
008 23/11/2015
From the data in the example the SQL should return: IDs: 111, 008
I tried an EXISTS subquery but it took forever to run on our slow machines so I gave up with that.
I also tried this left join SQL which ran really fast but for some reason included some IDs that have both dates, which is not what I want.
SELECT DISTINCT X.ID FROM (SELECT DISTINCT tblData.ID FROM tblData WHERE int([Tmst])=#23/11/2015#) AS X LEFT JOIN (SELECT DISTINCT tblData.ID FROM tblData WHERE int([Tmst])=#1/05/2015#) AS Y ON X.ID = Y.ID WHERE (((Y.ID) Is Null));
Regards
Deutz