CODEWITHSUNDEEP
sqlsql-servert-sql
AGH_TORN
AGH_TORN

Reputation: 833

Comparing SQL Server tables and finding matching outputs

I'm trying to compare two tables, A and B, to find instances where A exists but an identical record does not exist in B.

Table A:

|PROJECT|ID|USER|DATE|
+-------+--+----+----+
|   2   |1 |ASD |0624|
|   3   |2 |FGH |0624|

Table B:

|PROJECT|ID|USER|DATE|
+-------+--+----+----+
|   2   |1 |ASD |0624|

I'd like to see my output like this:

|PROJECT|ID|USER|DATE|MATCHING_ID|
+-------+--+----+----+-----------+
|   2   |1 |ASD |0624|     1     |
|   3   |2 |FGH |0624|    NONE   |

I've tried something like this but I just syntax or ambiguous column name errors. I'm not fully sure what I'm doing wrong. I've followed a few examples but ended up in the same situation.

SELECT [PROJECT], [ID],  [USER], [DATE]
FROM [TABLE_A] 
LEFT JOIN [TABLE_B] ON [ID] = [ID]
WHERE [DATE] >= DATEADD(mm, -2, GETDATE())

Upvotes: 0

Views: 42

Answers (2)

forpas
forpas

Reputation: 164154

You can use a CASE statement and EXISTS:

SELECT a.*,
CASE WHEN EXISTS (
  SELECT 1 FROM [TABLE_B] b
  WHERE a.PROJECT = b.PROJECT AND a.ID = b.ID AND a.USER = b.USER AND a.DATE = b.DATE
) THEN a.ID ELSE 'NONE' END MATCHING_ID
FROM [TABLE_A] a

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Fix your JOIN conditions and use COALESCE():

SELECT A.*, COALESCE(B.ID, 'NONE') as MATCHING_ID
FROM [TABLE_A] A LEFT JOIN
     [TABLE_B] B
     ON A.[ID] = B.[ID] AND
        A.[DATE] = B.[DATE] AND
        A.PROJECT = B.PROJECT AND
        A.USER = B.USER;

Upvotes: 1

Related Questions