SQL Join Return Null Without Duplicates

Question

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.

Answer 1

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.

Answer 2

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