Finding Exclusive Records

[Noonoo]

I'm sure what I'm trying to do is'nt that unusual but I can't find an answer anywhere.

All I want to do is find the records in one table without exact matches in another table. To put it another way records where Table1.Column1 <> Table2.Column1 AND Table1.Column2 <> Table2.Column2.

I need to do this is track down an import problem where all correct records are not getting selecting.

Thanks in advance for your time.

 

[MAK9974]

http://www.swynk.com/discuss_sql2000/GetMessage.asp?RootID=109198&ID=109209

 

[jcmv007]

What you need to do first is decide which table is going to be the primary table or the table with the correct record set. Let's say it is Table1.

Select
Table1.Column1,
Table2.Column1,
Table1.Column2,
Table2.Column2
from
Table1 left outer join Table2 on Table1.Column1 = Table2.Column1 AND Table1.Column2 = Table2.Column2

where
Table2.Column1 is null

With the left outer join you are show all values from table1 even thou they do not exists in table2. With the &quot;where table2.column1 is null&quot; you are only selecting the values that exist in table1 and table2 has no value.

Hope this helps with what you’re trying to do.

 

[fluteplr]

this would be cleaner and probably faster


select * from table1 where
not exists (select * from table2 where
Table1.Column1 = Table2.Column1 AND
Table1.Column2 = Table2.Column2)

 

[Noonoo]

Thanks people. That's worked and helped me solve the original problem.