SQL Syntax request...Please Help...

[new_phoenix 0]

I need to develop a query that identifies all of the records from one table that are not contained within another table. Inner Joins will not work. I know that there is an approach that will do this but I do not remember it.

[Luc Pattyn]

try

SELECT * FROM table1 WHERE field1 NOT IN (SELECT field2 FROM table2 WHERE ...)

:)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

[PIEBALDconsult]

Outer join

[Davewjones]

select * from table1 a left outer join table2 b on a.id=b.id where b.id is null This will give you all records in table1 that aren't in table2, assuming id is the common key.

[David Skelly]

There was a thread on here a few weeks ago about the merits of exists versus in versus outer join. I can't remember what the conclusion was and I can't find the thread now.

[J4amieC]

Exists was better.

[PIEBALDconsult]

Yes, I took part. I prefer outer join -- because of portability and such.