How to make this sql statement

Question

I have 2 tables:
T1(IDa,IDb) : has data like this

    IDa IDb  
    1   2  
    3   4  
    5   6  
    7   8

and T2(IDc,IDd) : with data like this

    IDc IDd  
    1   2  
    4   5  
    3   6  
    7   8  

and the Identity for each table is the pair of IDs:

• T1, Identity is IDa and IDb
• T2 is IDc and IDd

The question is: How to retrieve the "Not matched" records from the two tables??? In this case,

• the matched are 1,2 and 7,8
• the "not matched" are: 3,4 $ 5,6 $ 4,5 $ 3,6

I can do that using strings and concatenation. Did anyone have a method using inner join or any other method??

Answer 1

DECLARE @Result nvarchar(max)


SELECT @Result = ISNULL(@Result + '$','') + 
       CAST(ISNULL(IDa,IDc) AS VARCHAR(5)) + ',' +  
            CAST(ISNULL(IDb,IDd) AS VARCHAR(5))
FROM T1 FULL OUTER JOIN T2
ON T1.IDa = T2.IDc AND  T1.IDb = T2.IDd
WHERE T1.IDa IS NULL OR T2.IDc IS NULL

Edit Of course if the $ and , is not required just use

SELECT  ISNULL(IDa,IDc), ISNULL(IDb,IDd)
FROM T1 FULL OUTER JOIN T2
ON T1.IDa = T2.IDc AND  T1.IDb = T2.IDd
WHERE T1.IDa IS NULL OR T2.IDc IS NULL

Or another way, just for kicks (MS SQL Server 2005+)

SELECT IDa, IDb from T1
EXCEPT
SELECT IDc, IDd from T2
UNION ALL
(
SELECT IDc, IDd from T2
EXCEPT
SELECT IDa, IDb from T1
)