sqlsql-serversql-server-2005

Need a SQL Query for Getting matched and not matched Records


My table structures are shown here:

Table1

Col1
10
20
20
20
30
30
40

Table2

Col1
10
20
30
40

My expected result is this:

Col1 Col2
10 Matched
20 Matched
20 Not Matched
20 Not Matched
30 Matched
30 Not Matched
40 Matched

Query I'm trying to use:

SELECT 
    T1.Col1,
    CASE
        WHEN T2.Col1 IS NOT NULL THEN 'Matched'
        ELSE 'NotMatched'
    END AS Col2
FROM
    Table1 T1
LEFT JOIN 
    Table2 T2 ON T1.Col1 = T2.Col1;

Solution

  • We can stick with your join approach, but use ROW_NUMBER to keep track of occurrences:

    WITH cte1 AS (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) rn
        FROM Table1
    ),
    cte2 AS (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) rn
        FROM Table2
    )
    
    SELECT T1.Col1,
           CASE WHEN T2.Col1 IS NOT NULL THEN 'Matched' ELSE 'NotMatched' END AS Col2
    FROM cte1 T1
    LEFT JOIN cte2 T2
        ON T1.Col1 = T2.Col1 AND T1.rn = T2.rn
    ORDER BY T1.Col1, T2.Col1 DESC;