I need to join the following tables to create the below desired result:
Table A
| id_1 | id_2 | foo1 | foo2 |
|---|---|---|---|
| 123 | 456 | abc | def |
Table B
| id_1 | id_2 |
|---|---|
| 123 | 456 |
| 123 | 789 |
Current Result (duplicates)
| id_1 | id_2 | foo1 | foo2 |
|---|---|---|---|
| 123 | 456 | adb | def |
| 123 | 789 | abc | def |
The issue is that items foo1=adc and foo2=def do not correspond to id_2=789, and the join is creating duplicate line items for foo1 and foo2 because of the shared id_1.
Ideal Result (null, without duplicates)
| id_1 | id_2 | foo1 | foo2 |
|---|---|---|---|
| 123 | 456 | adb | def |
| 123 | 789 | null | null |
I've tried iterations of left, inner, and outer joins, to no avail.
CodePudding user response：
SELECT b.*, a.foo1, a.foo2
FROM Table A AS a
RIGHT JOIN Table B AS b
ON a.id_1 = b.id_1 AND a.id_2 = b.id_2
CodePudding user response：
Try it without the and on your join. It should return both
SELECT b.*, a.foo1, a.foo2 FROM Table A AS a RIGHT JOIN Table B AS b ON a.id_1 = b.id_1
(a.id_2 = b.id_2) essentially this is saying where a.id_2 is = b.id_2, so it won't return 789 since there is no matching id_2 for that record in table b.