CODEWITHSUNDEEP
mysql
Daniel lm
Daniel lm

Reputation: 97

JOIN SQL results from same table in one row and use values as column name

I need some help building a query and joining the results in one row, here are the tables:

tbl_contacts 

id | name   | lastname  | email
 1 | daniel1| lastname1 | [email protected] 
 2 | daniel2| lastname2 | [email protected]

tbl_contacts_var

cid | name | value| 
 1  | VAR1 | d1   | 
 1  | VAR2 | d2   | 
 1  | VAR3 | d3   |

I have tried the following query which is the closest desired result I could get, however I need it different, here is what I have tried:

SELECT tbl_contacts.*, 
( SELECT GROUP_CONCAT(value SEPARATOR ',') 
FROM tbl_contacts_var WHERE 
tbl_contacts.id = tbl_contacts_var.cid ) 
as attributes 
FROM tbl_contacts WHERE tbl_contacts.id = 1

And this is the output

 id | name   | lastname  | email           |attributes 
  1 | daniel1| lastname1 | [email protected]|d1,d2,d3

However, the output I am looking for is more like this:

  id | name   | lastname  | email           | VAR1 | VAR2 | VAR3 |
   1 | daniel1| lastname1 | [email protected]| d1   | d2   | d3   |

I tried with JOIN but it returns three rows, and I need the full result in one row, also I need to use the value of "name" in the second table as a column name for the result, is this possible?

Upvotes: 0

Views: 43

Answers (1)

Eric
Eric

Reputation: 3257

You can try the query below

SELECT c.name, c.name, c.lastname, c.email
    , MAX(
        CASE cv.name
            WHEN 'VAR1' THEN cv.VALUE
            ELSE NULL
        END
    ) AS VAR1
    , MAX(
        CASE cv.name
            WHEN 'VAR2' THEN cv.VALUE
            ELSE NULL
        END
    ) AS VAR2
    , MAX(
        CASE cv.name
            WHEN 'VAR3' THEN cv.VALUE
            ELSE NULL
        END
    ) AS VAR3
FROM tbl_contacts c
JOIN tbl_contacts_var cv ON cv.cid = c.id
GROUP BY c.name, c.name, c.lastname, c.email

Upvotes: 1

Related Questions