How can query the database with 3 foreign keys and 1 primary?

October 6, 2022

Bottom line:
I am populating a table (via a left join) in a view in asp.net mvc (not EF), where there are 3 foreign keys. The table is only populated with IDs and in the view I want to show the names Config instead. These are in the table with the primary key – hence the join.

Details (simplified for clarity):
TableA:

ID (pk)   Config
1         TGP      
2         BFM
3         BSA

TableB:

ID        ConfB_ID(fk)     ConfR_ID(fk)    ConfS_ID(fk)
1         1
2                          1               3 
3         3                2

What I want (with the query to generate the table in the view):
Result:

ConfB            ConfR           ConfS
TGP
                 TGP             BSA
BSA              BFM

My current query:

SELECT TableB.*, TableA.Config AS ConfB
FROM TableB LEFT JOIN TableA ON TableB.ConfB_ID = TableA.ID;

This works and I can populate the table correctly, but of course only for ConfB. How do I get ConfR and ConfS included in the query as well, so I can populate them as per my "expected result" above?

This is of course a simplified example – I would be happy to post everything with views/controllers etc. Just thought the actual query might be the key to solving it.

Thank you for your help.
Peter

>Solution :

select b.id,
  a.config as ConfB_Config ,a2.config as ConfR_config,a3.config as ConfS_Config
from tableb as b
left join tablea as a on b.ConfB_ID=a.id
left join tablea as a2 on b.ConfR_ID=a2.id
left join tablea as a3 on b.ConfS_ID=a3.id

Could you please try something like this