skip to Main Content

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


  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
    
    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search