SQL Multiple values from same column with inner join

Question

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

If you want first_name and last_name, you need to select them rather than meta_value — EternalHour
– EternalHour, Commented Sep 4, 2019 at 19:52
Sorry I see the confusion. The first name and last name are just examples, not fields. The first name and last name are stored in the meta value field — user2389087
– user2389087, Commented Sep 4, 2019 at 20:21
It would be easier to give you an answer if you provided the table structure. — EternalHour
– EternalHour, Commented Sep 4, 2019 at 20:40

forpas · Accepted Answer · 2019-09-04 20:48:25Z

1

You can do it if you group by ID, user_email and use conditional aggregation:

select 
  u.ID, u.user_email, 
  max(case m.meta_key when 'first_name' then m.meta_value end) first_name,
  max(case m.meta_key when 'last_name' then m.meta_value end) last_name
from wp_xxxxxx_users u inner join wp_xxxxxx_usermeta m
on u.ID = m.user_id
where m.meta_key IN ('last_name', 'first_name')
group by u.ID, u.user_email

answered Sep 4, 2019 at 20:48

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2389087 Over a year ago

Thank you, brilliant stuff. I'm such a novice when it comes to SQL

nbk · Accepted Answer · 2019-09-04 20:58:38Z

1

You didn't give any data. So this tables

 CREATE TABLE wp_xxxxxx_users  (
   ID  int ,  user_email varchar(20));
 INSERT INTO wp_xxxxxx_users  (id,user_email) VALUES 
   (1,'[email protected]'),
   (2,'[email protected]'),
   (3,'[email protected]');


CREATE TABLE wp_xxxxxx_usermeta
  (`ID` int, `user_id` int, `meta_key` varchar(14), `meta_value` varchar(12))
;

INSERT INTO wp_xxxxxx_usermeta
  (`ID`, `user_id`, `meta_key`, `meta_value`)
VALUES
  (1, 1, 'last_name' , 'Dallas' ),
  (2, 1, 'first_name' , 'Kermit' ),
  (3, 2, 'last_name' , 'Huston' ),
  (4, 2, 'first_name' , 'Piggy' ),
  (5, 3, 'last_name' , 'Oklahoma' ),
  (6, 3, 'first_name' , 'Beep' )
;

This Select statement

SELECT 
  wpu.ID
  ,wpu.user_email
  , wpm.last_name
  ,wpm2.first_name 
FROM wp_xxxxxx_users wpu
INNER JOIN 
(SELECT meta_value as last_name, user_id FROM
  wp_xxxxxx_usermeta Where meta_key = 'last_name') wpm  
  ON  wpu.ID=wpm.user_id
INNER JOIN (SELECT meta_value as first_name, user_id from
 wp_xxxxxx_usermeta WHERE meta_key = 'first_name') wpm2  
 ON  wpu.ID=wpm2.user_id
;

or you can use

Select       wpu.ID
  ,MAX(wpu.user_email), 
  MAX(CASE WHEN wpm.meta_key = 'last_name' THEN wpm.meta_value END) last_name,
  MAX(CASE WHEN wpm.meta_key = 'first_name' THEN wpm.meta_value END) first_name
  From wp_xxxxxx_users wpu inner join wp_xxxxxx_usermeta wpm 
  ON  wpu.ID=wpm.user_id
  WHERE  wpm.meta_key in ('last_name','first_name')
  GROUP by wpu.ID;

gives you following result

ID  user_email      last_name   first_name
1   [email protected]    Dallas      Kermit
2   [email protected]    Huston      Piggy
3   [email protected]   Oklahoma    Beep

You find here an example

answered Sep 4, 2019 at 20:58

nbk

49.6k8 gold badges32 silver badges53 bronze badges

2 Comments

user2389087 Over a year ago

Thank you for your help

nbk Over a year ago

sorry @forpas i had it as answer open and send it without checking if there is already an answer

Collectives™ on Stack Overflow

SQL Multiple values from same column with inner join

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related