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

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):

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

CodePudding user response：

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

CodePudding user response：

You can multiple left joins with different keys
use IFNULL if you want to avoid null values

SELECT TableB.*, 
    IFNULL(b.Config,"") AS ConfB,
    IFNULL(r.Config, "") AS ConfR,
    IFNULL(s.Config, "") AS ConfS
FROM TableB 
LEFT JOIN TableA b ON TableB.ConfB_ID = b.ID
LEFT JOIN TableA r ON TableB.ConfR_ID = r.ID
LEFT JOIN TableA s ON TableB.ConfS_ID = s.ID;

CodePudding user response：

You need 3-times join. Oracle SQL

with TableA (pk, Config) as (
select 1, 'TGP' from dual union all
select 2, 'BFM' from dual union all
select 3, 'BSA' from dual),
TableB (ID, ConfB_ID, ConfR_ID, ConfS_ID ) as (
select 1,  1   ,  null, null from dual union all
select 2,  null,  1   , 3    from dual union all 
select 3,  3   ,  2   , null from dual)
select
b.id, 
a0.config as confb_conf,
a1.config as confr_conf,
a2.config as confs_conf
from TableB b
left join TableA a0 on b.ConfB_ID = a0.pk
left join TableA a1 on b.ConfR_ID = a1.pk
left join TableA a2 on b.ConfS_ID = a2.pk
order by b.id;

        ID CONFB_CONF CONFR_CONF CONFS_CONF
---------- ---------- ---------- ----------
         1 TGP                   
         2            TGP        BSA
         3 BSA        BFM        

SQL>

https://dbfiddle.uk/tXkFHdTD