USER5615 USER5615 - 4 months ago 8
MySQL Question

Join Three tables in mysql with weird requirement

I have three tables in my db.

Table A
has the fields

KEYID | KeyName
27 | Income
28 | Account Number


Table B
has the fields

UserID | Email | Name | Phone
481 | test@gmail.com | test | 99999999


Table C
has the fields

ID | KEYID | UserID | Value
1 | 27 | 481 | 10,000


I need to display the table fields headers are:

UserID | Email | Name | Phone | Income


and the table values should be like this:

481 | test@gmail.com | test | 99999999 | 10,000


I can get the KeyIDs which should be displayed in the table. In this example the KeyIDs string is '27' . I tried with joining and i can fetch & display the value in the table. but i dont know how i can show the key name as table header.

Any Idea.?

Answer

You can use a pair of inner join

select b.UserID, b.Email , b.Name, c.value as income 
from   tableB as b inner join tableC as C on b.userID = c.userId
inner join tableA as a on a.keyID = c.keyID 
and a.keyname = 'Income';     

and the query you provided in comment

select 
      b.UserID
    , b.Email 
    , b.Name
    , Group_Concat(Distinct Concat(c.keyID,’^:^’,c.value) 
                          Order By c.id Separator ‘;’) As Keyvalues 
    from tableB as b 
    inner join tableC as C on b.userID = c.userId 
    inner join tableA as a on a.keyID = c.keyID;  

and with CASE should be

 select 
      b.UserID
    , b.Email 
    , b.Name
    , Group_Concat(Distinct CASE 
            WHEN c.keyID IN ('1,23,10') THEN Concat(c.keyID,’^:^’,c.value) END  
            Order By c.id Separator ‘;’) As Keyvalues 
    from tableB as b 
    inner join tableC as C on b.userID = c.userId 
    inner join tableA as a on a.keyID = c.keyID;