Kuzya987 Kuzya987 - 4 months ago 5
SQL Question

Multiple Join to single column using ID T-SQL

I have following issue. I'm trying to join three tables:

TableA: Record_id, Reference_id, Param

TableB: Doc_id, NameB

TableC: Doc_id, NameC

depending on value in TableA.Param, I need to select Name from tables B or C by joining on Reference_id

I've tried to use "case" constrain, but didn't worked out :)

select a.Record_id, a.Reference_id
, case when Param = 'B' then b.NameB
when Param = 'C' then c.NameC
from TableA as a inner join
TableB as b on a.Reference_id = b.Doc_id inner join
TableC as c on a.Reference_id = c.Doc_id


Any ideas?

Answer

You could left join with that condition and take the first non-null name with coalesce()

select  a.Record_id, a.Reference_id,
        coalesce(b.NameB, c.NameC) as name
from TableA as a 
left join TableB as b on a.Reference_id = b.Doc_id 
                     and Param = 'B'
                     and b.STAT = 'A'
left join TableC as c on a.Reference_id = c.Doc_id 
                     and Param = 'C'
                     and c.STAT = 'A'

BTW your query was close (missed the end). This would have worked with left joins

select  a.Record_id, a.Reference_id,
        case when Param = 'B' then b.NameB
             when Param = 'C' then c.NameC
        end as name
from TableA as a 
left join TableB as b on a.Reference_id = b.Doc_id and b.STAT = 'A'
left join TableC as c on a.Reference_id = c.Doc_id and c.STAT = 'A'
Comments