MySQL Join table on itself horizontally

Question

I've been trying to figure this out and I know it has to do with joins but can't seem to figure it out

When I run the following query:

SELECT user_id, meta_key, meta_value 
FROM usermeta 
WHERE usermeta.meta_key = 'my_key' 
OR usermeta.meta_key = 'first_name' 
OR usermeta.meta_key = 'last_name'

I get the data of course:

user_id    meta_key    meta_value
1          first_name  John
2          first_name  Jane
3          first_name  Tom
1          last_name   Doe
2          last_name   Cobb
3          last_name   Smith
1          my_key      ABC
2          my_key      DEF
3          my_key      GHI

I can't figure out the proper way to join this data to get:

user_id     first_name   last_name   my_key
1           John         Doe         ABC
2           Jane         Cobb        DEF
3           Tom          Smith       GHI

How can I achieve this in MySQL? Any help is appreciated.

If not, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Jul 14 '20 at 21:24

forpas · Accepted Answer · 2020-07-14T18:37:12.137

1

Group by id and use conditional aggregation:

SELECT user_id, 
       MAX(CASE WHEN meta_key = 'first_name' THEN meta_value END) first_name,
       MAX(CASE WHEN meta_key = 'last_name' THEN meta_value END) last_name,
       MAX(CASE WHEN meta_key = 'my_key' THEN meta_value END) my_key
FROM usermeta 
WHERE meta_key = 'my_key' 
   OR meta_key = 'first_name' 
   OR meta_key = 'last_name'
GROUP BY user_id

See the demo.
Results:

| user_id | first_name | last_name | my_key |
| ------- | ---------- | --------- | ------ |
| 1       | John       | Doe       | ABC    |
| 2       | Jane       | Cobb      | DEF    |
| 3       | Tom        | Smith     | GHI    |

Edit
You can join the above query to the other table:

SELECT g.user_id, g.first_name, g.last_name, g.my_key, t.email
FROM (
  <the query here>
) g INNER JOIN othertable t
ON t.id = g.user_id

edited Jul 14 '20 at 18:37

answered Jul 14 '20 at 16:01

forpas

160,666
10
38
76

HI, thank you! This works...but I forgot to mention that I have another table where the email address for those users would exist. The user_id values from the initial table in my question has the same values of the column "ID" in the other table(they're foreign keys), how would I join their respective email addresses onto the result of the GROUP BY so they're accurate and the correct email shows for the correct user? Not sure if i should update the question since you already answered it but i forgot to include that detail – Uriahs Victor Jul 14 '20 at 18:26
See my edited answer. – forpas Jul 14 '20 at 18:31

MySQL Join table on itself horizontally

1 Answers1