CODEWITHSUNDEEP
sqlsql-servert-sql
Pavlos Fragkiadoulakis
Pavlos Fragkiadoulakis

Reputation: 820

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

Upvotes: 3

Views: 1662

Answers (3)

TheGameiswar
TheGameiswar

Reputation: 28890

--one more way using cross apply

  select ta.aid,a1.*,a2.*
    from table1 ta
    cross apply(select tb.name from tableb tb where tb.aid=ta.bid1) a1
    cross apply(select tb.name from tableb tb where tb.aid=ta.bid2) a2

Upvotes: 3

Pham X. Bach
Pham X. Bach

Reputation: 5432

Just use one more join

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

Upvotes: 5

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

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

Upvotes: 8

Related Questions