Comparing 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

SQL Server 2005 Forums

Transact-SQL (2005)

Comparing Tables

Author

Topic

abernut
Starting Member

6 Posts

Posted - 2011-12-12 : 11:29:11

I need to compare tables and return records that only exist in one table and not the other.
ie...
table CUSTOMER contains every customer in the database.
table NOTES contains records when a note has been added to the customers account.

I need a way to return any customer that has not had a note added to his account, basically he has a record in CUSTOMER but nothing in NOTES.

I've found a few examples but they look for NULL values in the NOTES table but in my case, there isn't a record entry for the customer.

Thank you,

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-12-12 : 11:36:04

SELECT <stuff>
FROM CUSTOMER c
WHERE NOT EXISTS(select * from NOTES n where c.CustomerID = n.CustomerID) -- or whatever column joins the 2

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 11:36:22

[code]
SELECT c.*
FROM CUSTOMER c
WHERE NOT EXISTS (SELECT 1 FROM NOTES WHERE CustomerID = c.CustomerID)
[/code]
where CustomerID is common column between them (column name used is indicative so make sure you use correct column name instead)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abernut
Starting Member

6 Posts

Posted - 2011-12-12 : 11:36:48

let me give that a spin.
I will post my results.

Thanks
Mike

abernut
Starting Member

6 Posts

Posted - 2011-12-12 : 11:57:37

You sir, are a genius.

Thank you it does exactly what I needed to do.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources