SELECT ALL RECORDS EXCEPT

G

Guest

Hi, need the help of the gurus again.. and the help is appreciated in advance
yet again.

I have a TableA with values RED, BLUE, YELLOW, GREEN and TableB with values
RED, BLUE. would like to obtain the result of TableA of those records that
are not in TableB. Answer would be: YELLOW, GREEN. (this is in lamest terms)
the actual SQL Statement being used is:

SELECT DFIDUIINFO.filename, DFIDUIINFO.createSel
FROM DFIDUIINFO, [UIL SelectDI filenames]
WHERE DFIDUIINFO.filename <> [UIL SelectDI filenames].[filename];

but it ends up giving me all the records on both tables.. again thank you
for your help..
-Sam
 
M

[MVP] S.Clark

The Query you desire is based on set theory, so think about it in terms of a
Venn diagram. Each Circle of the diagram is a set of data.

Your current criteria statement is only comparing one control value against
all of the comparison values, thus, as each controvalue is evaluated, all of
the non-matching comparison values get returned.

SOOOOOO, you need to solve differently. You could put all records into a
temp table, then delete individually those that DO exist, leaving you only
with the ones that don't. This is called solving by the inverse.
 
M

Marshall Barton

samymelbourne said:
Hi, need the help of the gurus again.. and the help is appreciated in advance
yet again.

I have a TableA with values RED, BLUE, YELLOW, GREEN and TableB with values
RED, BLUE. would like to obtain the result of TableA of those records that
are not in TableB. Answer would be: YELLOW, GREEN. (this is in lamest terms)
the actual SQL Statement being used is:

SELECT DFIDUIINFO.filename, DFIDUIINFO.createSel
FROM DFIDUIINFO, [UIL SelectDI filenames]
WHERE DFIDUIINFO.filename <> [UIL SelectDI filenames].[filename];


Try using an outer join:

SELECT DFIDUIINFO.filename, DFIDUIINFO.createSel
FROM DFIDUIINFO LEFT JOIN [UIL SelectDI filenames]
ON DFIDUIINFO.filename = [UIL SelectDI filenames].filename
WHERE [UIL SelectDI filenames].filename Is Null
 
G

Guest

Thanks for the input I found and aswer to.. similar to the last posting
I got the following query to work...

SELECT DFIDUIINFO.filename
FROM (SELECT DFIDUIINFO.filename FROM DFIDUIINFO)
WHERE DFIDUIINFO.filename NOT IN (SELECT [UIL SelectDI filenames].filename
FROM [UIL SelectDI filenames]);

Thanks for the Help again! it let me to this answer...
 
G

Guest

Thanks for the input I found and aswer to.. similar to the last posting
I got the following query to work...

SELECT DFIDUIINFO.filename
FROM (SELECT DFIDUIINFO.filename FROM DFIDUIINFO)
WHERE DFIDUIINFO.filename NOT IN (SELECT [UIL SelectDI filenames].filename
FROM [UIL SelectDI filenames]);

Thanks for the Help again! it let me to this answer...

Marshall Barton said:
samymelbourne said:
Hi, need the help of the gurus again.. and the help is appreciated in advance
yet again.

I have a TableA with values RED, BLUE, YELLOW, GREEN and TableB with values
RED, BLUE. would like to obtain the result of TableA of those records that
are not in TableB. Answer would be: YELLOW, GREEN. (this is in lamest terms)
the actual SQL Statement being used is:

SELECT DFIDUIINFO.filename, DFIDUIINFO.createSel
FROM DFIDUIINFO, [UIL SelectDI filenames]
WHERE DFIDUIINFO.filename <> [UIL SelectDI filenames].[filename];


Try using an outer join:

SELECT DFIDUIINFO.filename, DFIDUIINFO.createSel
FROM DFIDUIINFO LEFT JOIN [UIL SelectDI filenames]
ON DFIDUIINFO.filename = [UIL SelectDI filenames].filename
WHERE [UIL SelectDI filenames].filename Is Null
 
M

Marshall Barton

Well that might work in your situation, but it may also be
pretty slow (two subqueries). Try the outer join, it's
simpler and faster.
--
Marsh
MVP [MS Access]


Thanks for the input I found and aswer to.. similar to the last posting
I got the following query to work...

SELECT DFIDUIINFO.filename
FROM (SELECT DFIDUIINFO.filename FROM DFIDUIINFO)
WHERE DFIDUIINFO.filename NOT IN (SELECT [UIL SelectDI filenames].filename
FROM [UIL SelectDI filenames]);

samymelbourne said:
Hi, need the help of the gurus again.. and the help is appreciated in advance
yet again.

I have a TableA with values RED, BLUE, YELLOW, GREEN and TableB with values
RED, BLUE. would like to obtain the result of TableA of those records that
are not in TableB. Answer would be: YELLOW, GREEN. (this is in lamest terms)
the actual SQL Statement being used is:

SELECT DFIDUIINFO.filename, DFIDUIINFO.createSel
FROM DFIDUIINFO, [UIL SelectDI filenames]
WHERE DFIDUIINFO.filename <> [UIL SelectDI filenames].[filename];
Marshall Barton said:
Try using an outer join:

SELECT DFIDUIINFO.filename, DFIDUIINFO.createSel
FROM DFIDUIINFO LEFT JOIN [UIL SelectDI filenames]
ON DFIDUIINFO.filename = [UIL SelectDI filenames].filename
WHERE [UIL SelectDI filenames].filename Is Null
 
J

John Vinson

SELECT DFIDUIINFO.filename
FROM (SELECT DFIDUIINFO.filename FROM DFIDUIINFO)
WHERE DFIDUIINFO.filename NOT IN (SELECT [UIL SelectDI filenames].filename
FROM [UIL SelectDI filenames]);

Whoa! A subquery within a subquery? That's going to be much slower
than a simple frustrated outer join:

SELECT DFIDUIINFO.filename
FROM DFIDUIINFO
LEFT JOIN [UIL SelectDI filenames]
ON DFIDUIINFO.filename = [UIL SelectDI filenames].filename
WHERE [UIL SelectDI filenames].filename IS NULL;


John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top