How to compare two tables - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

How to compare two tables

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-04-06 : 02:23:45

Hi,

I have two identical tables (my administrative staff did not update the database using the ERP, and instead store the information in Excel, *sigh*) named StudentA, and StudentB

The columns are studentID (PK), studentName, Address.
I would like a list of a studentID, studentName, AddressFromStudentA, AddressFromStudentB where the address from StudentA is different from studentB, and records of any students that are in StudentA, but not in Student B and vice versa.

e.g.
Assume I got the following information in StudentA table

StudentID , Student Name , Address
S1 , Test Student 1 , ABC Road
S2 , Test Student 2 , DEF Road
S4 , Test Student 3 , GHI Road

In studentB table, I got the following records

StudentID , Student Name , Address
S1 , Test Student 1 , ABC Road
S2 , Test Student 2 , XWZ Road
S3 , Test Student 4 , PLE Road

I would like to get

StudentID , Student Name , AddressA, AddressB
S2 , Test Student 2 , DEF Road, XWZ Road
S3 , Test Student 4 , NULL, PLE Road
S4 , Test Student 3, GHI Road, NULL

(s1 is not included since the addressA and address B are the same).

How do I construct a query? When I use inner join, any null studentId in either table records are not included at the moment. :(

E.g. Select A.StudentID, A.StudentName, A.Address as 'AddressA', B.Address as 'AddressB' from StudentA A, StudentB B where A.StudentID = B.StudentID and A.Address <> B.Address

What is wrong with my query? How do I ensure that any missing studentID will also be included? Any help is very much appreciated.

Thank you

Eugene

check out my blog at http://www.aquariumlore.blogspot.com

heavymind
Posting Yak Master

115 Posts

Posted - 2009-04-06 : 02:29:22

check out this article
http://db-staff.com/index.php/microsoft-sql-server/82-universal-method-work-with-sets
hopefully it will help you

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-04-06 : 02:41:09

Heavymind,

err... I am lost.. binary explainations makes me very fuzzy headed. I read the article twice, and I don't get it. Is there any simple method ?

I am lost at the bottom -> I got 2 tables, so what is the sum(binaryID) I should select to get all differenceS?

check out my blog at http://www.aquariumlore.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources