SQL One row that has multiple joins from different columns

Question

Mapping Table 

Desciption  ToID FromID 
Map A       2    1 


BaseTable

Id, Deccription, subTypeID
1   ValueA       9
2   ValueB       10`enter code here`

SubTypeTable
id  Description
9   SubType 9
10  Subtype 10

What I want to return is the following from the Mapping table

MapDescription, SubTpeDescription of ToID, SubTpeDescription of FromID

So Basically MapA, Subtype9,Subtype 10 as output

What I have go so far is

Select m.Description, st.Description from Mapping m 
right join BaseTable bt where m.toID = bt.id
right join BaseTable bt where m.FromID = bt.id,
inner join SubTypeTable stt on bt.subTypeID = stt.id

score 1 · Accepted Answer · answered Jun 22 '15 at 18:34

You need to give your table a different alias when you reference it the second time.

Select m.Description, st.Description 
FROM BaseTable bt 
LEFT JOIN Mapping m where m.toID = bt.id
LEFT JOIN BaseTable bt2 where m.FromID = bt2.id,
inner join SubTypeTable stt on bt2.subTypeID = stt.id

I personally would start with the main table (Base table) and work your way towards what you need. And I try to avoid Right joins - usually when I use them it's due to poor planning.

SQL One row that has multiple joins from different columns

1 Answers1