November 23, 2006 at 5:11 am
Hi, Could someone help with this issue issue?.
I have two tables containing inventory information and need to know the best of finding all records that exist in one table but not the other.
Thanks,
November 23, 2006 at 7:01 am
Select * from dbo.a where not exists (Select * from b where a.key = b.key)
or
Select * from a left outer join b on a.key = b.key where b.key is null
November 25, 2006 at 10:52 am
Since you are using SS 2005, you can do the following as well:
Select Field1, Field2, Field2, ..... FieldN From dbo.a
Except
Select Field1, Field2, Field2, ..... FieldN From dbo.b
This will compare the two recrodsets and return the values in the first not found in the second. This follows the same rules as a Union as both queries must have the same number of columns and the data types must match up.
And if you want to find all records that exist in both, use Intersect instead of Except.
November 28, 2006 at 12:32 pm
Goal: "All records that exist in one table but not the other"
I am assuming that the two tables have a common key, for my example I will use ID
Select a.Field1, a.Field2..., b.Field1, b.Field2 ... FROM [table1] a FULL OUTER JOIN [table2] b ON a.ID = b.ID WHERE a.ID IS NULL OR b.ID IS NULL
November 28, 2006 at 12:38 pm
Actually, no. I specifically demonstrated using column names instead of * to demonstrate that the key fields don't have to be the same. Only the fields in the queries have to be the same.
November 28, 2006 at 12:54 pm
"Actually, no. I specifically demonstrated using column names instead of * to demonstrate that the key fields don't have to be the same. Only the fields in the queries have to be the same."
I have gone back and edited my post, I think that there may have been some misunderstanding. I was not correcting anyone's post, I was just proposing another way of doing it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply