I am looking to join data from one table to another. The problem I have is in table two, the data I need is in the same column.
This is using wordpress/woocommerce DB so as you may be aware a lot of the user data is stored in the user meta table and you extract it by selecting the meta value where meta key = something
Rough data structure for user meta table
id | user_id | meta_key | meta_value
Now I select from table 1, inner join to table 2 and use the IN operator to select the meta values I want based on meta_key
select wp_xxxxxx_users.ID, wp_xxxxxx_users.user_email, wp_xxxxxxxxx.meta_value
from wp_xxxxxx_users
INNER JOIN wp_xxxxxx_usermeta on wp_xxxxxx_users.ID=wp_xxxxxx_usermeta.user_id
WHERE wp_xxxxxx_usermeta.meta_key IN ('last_name', 'first_name')
Now this returns all the data fine but in this format
id | user_email | meta_value
So what’s wrong? Well I would like the meta_value columns to appear on the same row rather than multiple rows
I want the output to do the following
id | user_email | metavalue-first_name | metavalue-last_name
As it’s currently outputting like so
1 | [email protected] | first name
1 | [email protected] | lastname name
2 | [email protected] | first name
2 | [email protected] | lastname name
I have a looked around relevant threads and seen people accomplishing this by Group By but I couldn’t get it to work
Version is MySQL 5.7.27
2
Answers
You can do it if you group by ID, user_email and use conditional aggregation:
You didn’t give any data.
So this tables
This Select statement
or you can use
gives you following result
You find here an example