CODEWITHSUNDEEP
mysql
user2389087
user2389087

Reputation: 1762

SQL Multiple values from same column with inner join

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

Upvotes: 1

Views: 2407

Answers (2)

nbk
nbk

Reputation: 49373

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

Upvotes: 1

forpas
forpas

Reputation: 164064

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

Upvotes: 1

Related Questions