CODEWITHSUNDEEP
sqlsql-server
ss ss
ss ss

Reputation: 3

How to join two tables with unequal number of rows for same primary key value

I want to join two same tables with different data for the same primary key value.

I am performing a full join between the two tables, as I want to see what information has changed for the same ID between two months. I tried using a group by clause as well, but that didn't work.

Select 
    a.ID, a.Value1,
    b.Value
from 
    TableA a
full join 
    TableB b on a.ID = b.ID

Input data:

Table A         Table B

ID  Value       ID  Value
---------       ----------
1   A           1   A
1   B           1   B
2   A           1   C

Desired output:

ID VALUE1  Value2
-----------------
1   A       A
1   B       B
1   Null    C

Current (wrong) output:

ID VALUE1  Value2
-----------------
1   A       A
1   A       B
1   A       C
1   B       A
1   B       B
1   B       C

Upvotes: 0

Views: 1072

Answers (2)

Arulkumar
Arulkumar

Reputation: 13247

You can use TableB in FROM then TableA in LEFT JOIN and in your ON clause add the Value matching too, to achieve your expected result:

SELECT B.ID,
       A.Value AS Value1,
       B.Value AS Value2
FROM TableB B
LEFT JOIN TableA A ON A.ID = B.ID AND A.Value = B.Value;

Demo with sample data:

DECLARE @TableA TABLE (ID INT, [Value] VARCHAR (1));

INSERT INTO @TableA (ID, [Value]) VALUES
(1, 'A'), (1, 'B'), (2, 'A');

DECLARE @TableB TABLE (ID INT, [Value] VARCHAR (1));

INSERT INTO @TableB (ID, [Value]) VALUES
(1, 'A'), (1, 'B'), (1, 'C');

SELECT B.ID,
       A.[Value] AS Value1,
       B.[Value] AS Value2
FROM @TableB B
LEFT JOIN @TableA A ON A.ID = B.ID AND A.[Value] = B.[Value];

Output:

ID  Value1  Value2
----------------------
1   A       A
1   B       B
1   NULL    C

Upvotes: 0

donPablo
donPablo

Reputation: 1959

I suspect that ALL combinations are desired, thus FULL Join is better.

Select Case When a.ID IS Null Then b.ID Else a.ID End as ID,
       a.Value,
       b.Value
from UnequalRowsTableA a
     full join UnequalRowsTableB b on a.ID=b.ID and a.Value = b.Value

Results

ID  Value   Value
1   A         A         
1   B         B         
2   A         NULL
1   NULL      C

Upvotes: 1

Related Questions