SQL-JOIN ON TWO COLUMNS

user_4399917 0
Viewed 41

I have two tables (Table A and Table B). But I can't be able to bring the results I want.

Table_A (with FK id's)

enter image description here

Table_B(Foreignkey)for TABLE_A

enter image description here

I want results like this after joining tables.

enter image description here

This is what I am trying

SELECT
A.COL_1,
B.NAME,
BB.NAME

FROM TABLE_A AS A

LEFT JOIN TABLE_B AS B ON B.ID = A.ID

LEFT JOIN TABLE_B AS BB ON BB.ID = A.ID

sql-server
2 Answers

You got it almost right, but on that table definition the codes to join with B are not A.ID, but A.NAME and A.SPECIALNAME

SELECT A.COL_1, B.NAME AS NAME, BB.NAME AS SPECIALNAME
FROM TABLE_A AS A
     LEFT JOIN TABLE_B AS B ON B.ID = A.NAME
     LEFT JOIN TABLE_B AS BB ON BB.ID = A.SPECIALNAME

PS: Don't forget to also set an alias on the result for B.NAME and BB.NAME, so you can differentiate them.

user_62203770

Please try this:

SELECT
    A.COL_1,
    B.NAME,
    BB.NAME AS SPECIALNAME
FROM TABLE_A AS A
LEFT JOIN TABLE_B AS B  ON B.ID = A.NAME
LEFT JOIN TABLE_B AS BB ON BB.ID = A.SPECIALNAME

If TABLE_A has non-null Foreign Key References, then you could use (INNER) JOIN instead of LEFT JOIN.

user_57447180
Related