MySQL Join Many to Many as single rows

Question

I know there's got to be a way to do this, but for the life of me I can't figure it out:

I have 3 tables I want to join together (simplified to illustrate):

users
uid mail
1   [email protected]
2   [email protected]
3   [email protected]

profile_fields
fid name        label
1   full_name   Full Name
2   phone       Phone

profile_values
uid fid value
1   1   Q Q
1   2   5555555555
2   1   Ww Ww
3   2   4444525411

I'd like to get results of the form:

uid mail        full_name phone
1   [email protected]   Q Q  5555555555
2   [email protected]   Ww Ww  NULL
3   [email protected]   NULL  44445454111

I've tried various SELECTs with different JOIN conditions but I can't seem to figure out how to get the rows of profile_fields to be my columns in my SELECT

EDIT: I've also tried googling around, but I can't seem to figure out how to phrase this to google.

Answer 1

Use:

SELECT u.uid,
       u.mail,
       MAX(CASE WHEN pf.name = 'full_name' THEN pv.value END) AS full_name,
       MAX(CASE WHEN pf.name = 'phone' THEN pv.value END) AS phone
  FROM USERS u
  LEFT JOIN PROFILE_VALUES pv ON pv.uid = u.uid
  JOIN PROFILE_FIELDS pf ON pf.fid = pv.fid
                        AND pf.name IN ('full_name', 'phone')
GROUP BY u.uid, u.mail

Answer 2

What you are trying to do is called a pivot. MySQL doesn't support pivoting natively, but you can do it using the query OMG Ponies posted.

However, if you have to support an arbitrary number of profile fields, you would have to build the SQL dynamically.

Answer 3

I think that in general you can't do what you want. Even if @OMG Ponies example is correct it won't work for other values of profile_field names.

You can try writing some code to generate a query for different values of profile_fields based on actual profile_fields.

Or you can make simple many-to-many join and analyse data in other program/code.