CODEWITHSUNDEEP
sqlsql-serverdatabaseselectjoin
Leth
Leth

Reputation: 1059

Inner join multiple ID columns with ID in foreign table to display multiple name columns

I need to make a query that inner joins 3 different id's from one table with the id from another, to then display the name value from that table in my select query. I'll try to make it a bit more clear.

In my one table I have these 3 columns with id's:

Book_Kalender.BS_ID,
Book_Kalender.BS_ID_Prio2,
Book_Kalender.BS_ID_Prio3,

These all need to be inner joined with a column in another table, which contains the name associated with these ids:

Book_Sommerhuse.[BS_ID]

In my SELECT query I am including the name column from the foreign table. I want to instead have 3 columns, each with the associated name that corresponds to the ID.

Book_Sommerhuse.BS_Navn

So far I have tried to make multiple inner joins using the AND keyword:

INNER JOIN Book_Kalender ON Book_Sommerhuse.[BS_ID] = Book_Kalender.[BS_ID] 
AND Book_Sommerhuse.[BS_ID] = Book_Kalender.[BS_ID_Prio2]

But this returns and empty view from my select query. I'm also not sure how to create new columns for each name associated with the ID.

Full query:

SELECT
    Book_Kalender.BK_ID,
    Book_Kalender.BK_DatoFra,
    Book_Kalender.BK_DatoTil,
    Book_Kalender.BK_M_Navn,
    Book_Kalender.BK_M_Adr,
    Book_Kalender.BK_M_PostBy,
    Book_Kalender.BK_M_Afd,
    Book_Kalender.BK_M_MedArbNr,
    Book_Kalender.BK_M_Tlf,
    Book_Kalender.BK_M_Email,
    Book_Kalender.BK_Tidl_Lejet,
    Book_Kalender.BK_Tidl_Lejet_Txt,
    Book_Kalender.BS_ID,
    Book_Kalender.BS_ID_Prio2,
    Book_Kalender.BS_ID_Prio3,    
    A.BS_Navn as BS_Navn1,
    B.BS_Navn as BS_Navn2,
    c.BS_Navn as BS_Navn3,
    coalesce(A.BS_Navn,B.BS_Navn,c.BS_Navn) as BS_Navn    
FROM
    Book_Kalender
    LEFT JOIN Book_Sommerhuse A ON 
        Book_Kalender.BS_ID       = A.BS_ID
    LEFT JOIN Book_Sommerhuse B ON 
        Book_Kalender.BS_ID_Prio2 = B.BS_ID
    LEFT JOIN Book_Sommerhuse C ON 
        Book_Kalender.BS_ID_Prio3 = C.BS_ID    
WHERE
    Book_Kalender.BK_DatoFra BETWEEN #10/15/2017# AND #12/31/2018#;

Upvotes: 0

Views: 7107

Answers (3)

varun madikunta
varun madikunta

Reputation: 18

Use could use derived table which could provide you three columns data as a single column and then you could apply the join , something like this

INNER JOIN (
              SELECT BS_IDs FROM Book_Kalender CROSS APPLY(
               VALUES (BS_ID), (BS_ID_Prio2), (BS_ID_Prio3)) Cols(BS_IDs) 
           ) DerivedBook_Kalender   ON Book_Sommerhuse.[BS_ID] = DerivedBook_Kalender.[BS_IDs]

Upvotes: 0

Indent
Indent

Reputation: 4967

You need 3 left join :

select
    Book_Kalender.*,
    A.BS_Navn as BS_Navn1,
    B.BS_Navn as BS_Navn2,
    C.BS_Navn as BS_Navn3,
    coalesce(A.BS_Navn,B.BS_Navn,c.BS_Navn) as BS_Navn -- first non null BS_Navn
from      
    Book_Kalender
    LEFT JOIN Book_Sommerhuse A ON 
        Book_Kalender.BS_ID       = A.BS_ID
    LEFT JOIN Book_Sommerhuse B ON 
        Book_Kalender.BS_ID_Prio2 = B.BS_ID
    LEFT JOIN Book_Sommerhuse C ON 
        Book_Kalender.BS_ID_Prio3 = C.BS_ID

Upvotes: 2

Mazhar
Mazhar

Reputation: 3837

SELECT
     K.BS_ID
    ,S1.BS_Navn
    ,K.BS_ID_Prio2
    ,S2.BS_Navn 'Prio2_BS_Navn'
    ,K.BS_ID_Prio3
    ,S3.BS_Navn 'Prio3_BS_Navn'
FROM
    Book_Kalender   K
LEFT JOIN
    Book_Sommerhuse S1 ON S1.BS_ID = K.BS_ID
LEFT JOIN
    Book_Sommerhuse S2 ON S2.BS_ID = K.BS_ID_Prio2
LEFT JOIN
    Book_Sommerhuse S3 ON S3.BS_ID = K.BS_ID_Prio3

Upvotes: 0

Related Questions