asked
Compare two queries
I have two queries that I am trying to compare to find which records exist in one and not the other. My first query retruns 523 rows SELECT * FROM MasterList a1 LEFT JOIN tblInfo a2 ON a1.FIRSTNAME=a2.FirstName AND a1.LASTNAME=a2.LastName AND a1.DOB=a2.Birthdate WHERE
a2.ID IS null My second query returns 105 Rows SELECT * FROM MasterList a1 LEFT JOIN tblInfo a2 ON
a1.ID=a2. WHERE
a2.ID IS null Now I have done a UNION to get a distinct list of records from both. But what I also want is a query that shows me What records in query 1 or also in query 2 and vice versa...Any ideas? I tried an exists clause t but not sure if I did it correctly? Below was my attempt it is teurning 523 rows still SELECT * FROM MasterList a1 left JOIN tblInfo a2 ON a1.FIRSTNAME=a2.FirstName AND a1.LASTNAME=a2.LastName AND a1.DOB=a2.Birthdate WHERE
a2.ID IS NULL AND EXISTS ( SELECT * FROM MasterList a1 left JOIN tblInfo a2 ON
a1.ID=
a2.ID WHERE
a2.ID IS NULL )
use [EXCEPT][1] without copying and pasting your entire query, this gives you everything from the first query that's different or new from the second query: select * from query1 EXCEPT select * from query2 and this gives you everything in the second query that's different or new in the first query select * from query2 EXCEPT select * from query1 [1]:
http://msdn.microsoft.com/en-us/library/ms188055.aspx
> But what I also want is a query that > shows me What records in query 1 or > also in query 2 and vice versa...Any > ideas? I tried an exists clause t but > not sure if I did it correctly I am not sure either what you are after ;) @Ken Johnson thinks it is the case for EXCEPT and I got the feeling it is the case for INTERSECT ;) Only you know which one suits your situation select * from query1 INTERSECT select * from query2 and vice versa ...;)
8 People are following this question.
