I'm creating a stored procedure where I want to return a particular result set
I have 2 data sets that I'm trying to somehow join so that it only returns the outlier records (where there not a match). I've thought about using UNION and EXCEPT but it doesn't seem to work with this scenario. TO make this less complicated, i currently have two CTE in my proc (alternatively i can use #TempTables).
Query result 1. In the below result set, this query will return 3 fields. Field3 text value will always be the same here.
Field1 Field2 Field3
123 BAK 'Missing in Query 2'
234 HAO 'Missing in Query 2'
345 OPP 'Missing in Query 2'
Query result 2. Same deal here, Field3 will always have the same text value.
Field1 Field2 Field3
123 BAK 'Missing in Query 1'
234 HAO 'Missing in Query 1'
678 UTO 'Missing in Query 1'
DESIRED RESULT: The reason why these two are returned the first row (Field 345), is Missing in Query 2 and the 2nd row is missing in Query 1. I'm only looking for matches where Query1.Field1=Query2.Field1 and Query1.Field2 = Query2.Field2.
Field1 Field2 Field3
345 OPP 'Missing in Query 2' <- from Query 1
678 UTO 'Missing in Query 1' <- from Query 2
I've tried to use a FULL JOIN to do this, but FULL JOIN returns additional 3 columns with NULL values. I'm trying to avoid that and only display the result as shown above. Any help would be appreciated.
I think you want the rows that are not in both tables. One method is:
select qr1.*
from qr1
where not exists (select 1 from qr2 where qr2.field1 = qr1.field1 and qr2.field2 = qr1.field2)
union all
select qr2.*
from qr2
where not exists (select 1 from qr1 where qr1.field1 = qr2.field1 and qr1.field2 = qr2.field2);