Mysql JOIN Select query with key in one field and value in another field
I need to indicate that I am trying to join
table user
id | name | age
----------------
1 | Dave | 23
2 | Dane | 65
3 | Sam | 15
table user_profile
user_id | profile_key | profile_value
------------------------------------------------
1 | prfle.about | This is about Dave bio
1 | prfle.cntry | Germany
2 | prfle.email | dane@somewhere.com
1 | prfle.email | dave@somewhere.com
3 | prfle.about | This something about Sam
2 | prfle.cntry | Canada
I want to select two tables as
id | name | age | prfle.about | prfle.email | prfle.cntry
-------------------------------------------------------------------------
1 | Dave | 23 | This is about Dave bio | dave@somewhere.com | Germany
2 | Dane | 65 | null | dane@somewhere.com | Canada
3 | Sam | 15 | This something about Sam | null | null
This is my request
SELECT u.id, u.name, u.age, p.user_id, p.profile_key, p.profile_value
FROM user as u
LEFT JOIN user_profile as p
ON u.id = p.user_id
But when I execute it, I get something like a Cartesian union.
I would be glad if someone can help me
+3
source to share
1 answer
If your values are profile_key
limited, you can use the following approach to create a pivot table as
select
u.*,
max(case when up.profile_key = 'prfle.about' then up.profile_value end ) as `prfle.about`,
max(case when up.profile_key = 'prfle.cntry' then up.profile_value end ) as `prfle.cntry`,
max(case when up.profile_key = 'prfle.email' then up.profile_value end ) as `prfle.email`
from user u
left join user_profile up on up.user_id = u.id
group by u.id
+1
source to share