compare data in tables

[osmurf]

I have two tables and i want to make a query that returns the difference. I have tried:

SELECT person1.*
FROM dbo.REL_IMS_PERSON person1
WHERE (NOT EXISTS(
SELECT person2.*
FROM dbo.REL_IMS_PERSON_ODS person2
WHERE person1.id = person2.id AND person1.fn = person2.fn))

This works fine as long as none of the fields is null.
Any suggestions or other ways of comparing two tables? Any help would be greatly appreciated.

 

[tlbroadbent]

Try a left join query. This should work unless ID has Null values.

SELECT person1.*
FROM dbo.REL_IMS_PERSON person1
LEFT JOIN dbo.REL_IMS_PERSON_ODS person2
ON person1.id = person2.id
AND person1.fn = person2.fn
WHERE Person2.ID Is Null

If your key values can have Nulls, you'll always have trouble finding matches or non-matches. You may want to add the following criterion to the query.

AND person1.ID Is Not Null Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.