CODEWITHSUNDEEP
sqlsql-server
huza
huza

Reputation: 107

SQL Join when there are duplicate rows with different column

I'm trying to join these two tables, but it ends up showing duplicates values. Is there a way to achieve this?

+---------+-----------+------+------------+
| Table A |           |      |            |
+---------+-----------+------+------------+
| SeqID   | ID        | Part | PartNumber |
+---------+-----------+------+------------+
| 1       | ComputerA | Case | 111        |
+---------+-----------+------+------------+
| 2       | ComputerA | RAM  | 222        |
+---------+-----------+------+------------+
| 3       | ComputerA | RAM  | 333        |
+---------+-----------+------+------------+

+---------+-----------+------+----------+
| Table B |           |      |          |
+---------+-----------+------+----------+
| SeqID   | ID        | Part | SendDate |
+---------+-----------+------+----------+
| 412     | ComputerA | Case | 2001/3/5 |
+---------+-----------+------+----------+
| 413     | ComputerA | RAM  | 2001/3/5 |
+---------+-----------+------+----------+
| 414     | ComputerA | RAM  | 2001/3/5 |
+---------+-----------+------+----------+

Let's say we have these 2 tables.

I want result to be like below

+---------+-----------+------+------------+----------+
| Table C |           |      |            |          |
+---------+-----------+------+------------+----------+
| SeqID   | ID        | Part | PartNumber | SendDate |
+---------+-----------+------+------------+----------+
| 412     | ComputerA | Case | 111        | 2001/3/5 |
+---------+-----------+------+------------+----------+
| 413     | ComputerA | RAM  | 222        | 2001/3/5 |
+---------+-----------+------+------------+----------+
| 414     | ComputerA | RAM  | 333        | 2001/3/5 |
+---------+-----------+------+------------+----------+

, but when I run the code

   select b.seqid, b.id,b.part,a.partnumber,b.senddate from TableB b join TableA a on b.id = a.id and b.part = a.part group by b.seqid, b.id,b.part,a.partnumber,b.senddate order by b.seqid desc

I get below table

+---------+-----------+------+------------+----------+
| Table C |           |      |            |          |
+---------+-----------+------+------------+----------+
| SeqID   | ID        | Part | PartNumber | SendDate |
+---------+-----------+------+------------+----------+
| 412     | ComputerA | Case | 111        | 2001/3/5 |
+---------+-----------+------+------------+----------+
| 413     | ComputerA | RAM  | 222        | 2001/3/5 |
+---------+-----------+------+------------+----------+
| 413     | ComputerA | RAM  | 333        | 2001/3/5 |
+---------+-----------+------+------------+----------+
| 414     | ComputerA | RAM  | 222        | 2001/3/5 |
+---------+-----------+------+------------+----------+
| 414     | ComputerA | RAM  | 333        | 2001/3/5 |
+---------+-----------+------+------------+----------+

I see why this is happening and I think something is lacking on the condition part. Is there a way to achieve what I was expecting?

Upvotes: 2

Views: 42

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31785

One way to achieve your desired result, based on your sample data, is to generate a ROW_NUMBER() column for each of the two tables, partitioned by ID and Part and ordered by SeqID, and then include those Row_Numbers in the JOIN condition.

Upvotes: 3

mkRabbani
mkRabbani

Reputation: 16908

Try this below-

SELECT B.SeqID,
A.ID,
A.Part,
A.PartNumber,
B.SendDate 
FROM Table_A A
INNER JOIN (
    SELECT *,ROW_NUMBER() OVER(ORDER BY SeqID) RN
    FROM Table_B
)B
ON A.SeqID = B.RN

Upvotes: 0

Related Questions