Need help with a query to find non-matches

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help with a query to find non-matches

Author

Topic

j_mact
Starting Member

17 Posts

Posted - 2006-05-31 : 09:56:58

All,
I have two tables that contain student information. What I have done is two write a query to find the student records that match based on student number and social security number. What I need to do is to write a query to find the information that is not the same. i.e. what is in table A that is NOT in table B.

Any help would be appreciated

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-31 : 10:06:08

There are 2 appraches for this

Select A.* From TableA A Left Outer Join Tableb B On
A.Pk = B.Fk
Where b.fk is null
OR
Select * From TableA a Where A.pk Not in (Select fk from TableB)

Post same sample data with expected out if you want extact query

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-31 : 10:32:26

Since it seems the relationship between your 2 tables is based on more than one column, you might need to use not exists rather than not in...

Select * From TableA a Where Not exists (Select * from TableB where 
    StudentNumber = a.StudentNumber and SocialSecurityNumber = a.SocialSecurityNumber)

As chiragkhabaria refers, though, examples are always helpful to all concerned