Pavlos Fragkiadoulakis Pavlos Fragkiadoulakis - 6 months ago 9
SQL Question

INNER JOIN for two columns of the same foreign key

Suppose that we have two tables:

TABLE TA

AID BID1 BID2
-- ---- ----
01 01 02
02 01 03
03 02 01


TABLE TB

BID Name
--- ----
01 FOO
02 BOO
03 LOO


If I want to return the following:

AID Name1
-- -----
01 FOO
02 FOO
03 BOO


I write the following:

SELECT TA.AID, TB.Name as Name1
FROM TB
INNER JOIN TA on TB.BID = TA.BID1


However, I cannot figure out how to return the TB.Name that correspond to both the BID1 and BID2. More specifically I want to return the following:

AID Name1 Name2
-- ----- -----
01 FOO BOO
02 FOO LOO
03 BOO FOO

Answer

You could join multiple times:

SELECT TA.AID, tb1.Name AS Name1, tb2.Name AS Name2
FROM TA
LEFT JOIN TB tb1
  ON TA.BID1 = tb1.BID
LEFT JOIN TB tb2
  ON TA.BID2 = tb2.BID;

Note: LEFT OUTER JOIN will ensure you always get all records from TA even if there is no match.

LiveDemo

Comments