CODEWITHSUNDEEP
mysql
kiran babu
kiran babu

Reputation: 1

Table join with case

We would have to join 4 tables based on the table 1 column itemtype.

table A contains id,itemtype,itemid

table B contains id, fullname

table c contains id, fullname

table D contains id, fullname

Table A.itemid is in(table B or table C or table D) of id.

I want to join Table A with remaing tables to get full name. based on the TableA column itemtype value join another one table

     select
     tblA.itemid,y.fullname from tableA as tblA 
      inner join (
        CASE WHEN tblA.itemtype = 1 THEN 
          select
            tblB.itemid as id,tblB.fullname  as fullname
          from
            tableB    as tblB 
          where
            tblB.id = tblA.itemid
        WHEN tblA.itemtype = 2 THEN  
          select
            tblC.itemid as id,tblC.fullname  as fullname
          from
            tableC  as tblC  
          where
            tblC.id = tblA.itemid
            WHEN tblA.itemtype = 3 THEN 
              select
            tblD.itemid as id ,tblD.fullname as fullname
          from
            tableD  as tblD  
          where
            tblD.id = tblA.itemid

          END
      ) as bcd on bcd.id = tblA.itemid

Upvotes: 0

Views: 67

Answers (2)

profimedica
profimedica

Reputation: 2840

If there are not many columns in the association tables:

SELECT
   a.itemid,
   COALESCE(b.fullname, c.fullname, d.fullname, ' - Missing name - '))
FROM
   tblA a 
   LEFT JOIN tblB b ON a.itemtype = 1 AND a.itemid = a.id
   LEFT JOIN tblC c ON a.itemtype = 2 AND a.itemid = b.id
   LEFT JOIN tblD d ON a.itemtype = 3 AND a.itemid = c.id

Upvotes: 0

forpas
forpas

Reputation: 164224

You can left join the tables B, C and D to table A and include the condition for the column itemtype in the ON clause:

select
  A.itemid,
  coalesce(B.fullname, C.fullname, D.fullname) fullname
from A 
left join B on B.id = A.itemid AND A.itemtype = 1
left join C on C.id = A.itemid AND A.itemtype = 2
left join D on D.id = A.itemid AND A.itemtype = 3

See the demo.
Or with UNION ALL:

select
  A.itemid,
  B.fullname
from A inner join B on B.id = A.itemid 
where A.itemtype = 1
union all
select
  A.itemid,
  C.fullname
from A inner join C on C.id = A.itemid 
where A.itemtype = 2
union all
select
  A.itemid,
  D.fullname
from A inner join D on D.id = A.itemid 
where A.itemtype = 3

See the demo.

Upvotes: 1

Related Questions