October 21, 2002 at 11:24 am
I have two tables with a numeric field 'BRANDS'. I am trying to determine the difference between the two tables (e.g., what brand numbers does Table1 have that Table2 does NOT have). I thought this would be easy:
SELECT DISTINCT dbo.TM1.ID, dbo.TM1.ROLLUP, dbo.TM1.Brand
FROM dbo.TM1
INNER JOIN dbo.Monty ON dbo.TM1.Brand <> dbo.Monty.brand
This is not producing the desired results which would be a listing of brand numbers that do not exist in the 'Monty' table. Any ideas? Thank you!
October 21, 2002 at 12:03 pm
Try something like this:
SELECT DISTINCT dbo.TM1.ID, dbo.TM1.ROLLUP, dbo.TM1.Brand
FROM dbo.TM1
left outer JOIN dbo.Monty ON dbo.TM1.Brand = dbo.Monty.brand
where dbo.monty.Brand is null
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 21, 2002 at 12:32 pm
It worked great! Thank you very much!
Geoff
October 21, 2002 at 3:30 pm
Even better is to use the FULL JOIN this will show records missing in both tables
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply