Combine multiple rows from multiple tables in MySQL

Question

I have three tables;

user table

   id     first_name     last_name     country 
   2      John           Doe           USA
   3      Jimmy          Biglips       FRA

user_meta (metadata) table

user_id   meta_key       meta_value
   2      phone          3333
   2      rating         Good
   3      phone          7777
   3      rating         Bad

country table

country_id     country     country_fullname
    1          USA         United States of America
    2          FRA         France

I need to query by "rating" (e.g. "good") and return the result set as;

id  first_name    last_name     phone     rating    country     country_fullname 
2   John          Doe           33333     Good      USA         United States of America

Because the user_meta table has multiple rows that corresponds to a member in the user table, I was having trouble returning a combined set of values in one row.

Thank you.

Joachim Isaksson · Accepted Answer · 2013-08-05 09:27:10Z

2

You can just combine them in a JOIN. The LEFT JOINs are there to allow for the row to show up even if the user has no phone/rating available;

SELECT u.id, u.first_name, u.last_name, p.meta_value phone, r.meta_value rating,
       u.country, c.country_fullname
FROM user u
LEFT JOIN user_meta p ON u.id=p.user_id AND p.meta_key='phone'
LEFT JOIN user_meta r ON u.id=r.user_id AND r.meta_key='rating'
JOIN country c ON u.country=c.country;

An SQLfiddle to test with.

answered Aug 5, 2013 at 9:27

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

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

1 Comment

userabuser Over a year ago

Thanks for your help, consequently, I had to use INNER JOINs as opposed to LEFT JOINs. Otherwise I was receiving an empty result set. Could be an environment issue as I am passing this statement, in a slightly more complex form via a WordPress SQL statement i.e. $wpdb->get_results( SQL );. None the less, I've reached a reasonable outcome. Thanks again.

peterm · Accepted Answer · 2013-08-05 09:35:26Z

2

Try

SELECT u.id,
       u.first_name,
       u.last_name,
       MIN(CASE WHEN meta_key = 'phone' THEN meta_value END) phone,
       MIN(CASE WHEN meta_key = 'rating' THEN meta_value END) rating,
       u.country,
       c.country_fullname
  FROM user u LEFT JOIN user_meta m 
    ON u.id = m.user_id LEFT JOIN country c
    ON u.country = c.country
 GROUP BY u.id

Output:

| ID | FIRST_NAME | LAST_NAME | PHONE | RATING | COUNTRY |         COUNTRY_FULLNAME |
-------------------------------------------------------------------------------------
|  2 |       John |       Doe |  3333 |   Good |     USA | United States of America |
|  3 |      Jimmy |   Biglips |  7777 |    Bad |     FRA |                   France |

Here is SQLFiddle demo

edited Aug 5, 2013 at 9:35

answered Aug 5, 2013 at 9:26

peterm

93k16 gold badges157 silver badges162 bronze badges

1 Comment

userabuser Over a year ago

Thanks for your contribution @peterm. I couldn't get this to work precisely as I wanted in my environment (see above comment) however the fiddle works no problem and this was much easier on the eye to read. +1 Thanks again.

Collectives™ on Stack Overflow

Combine multiple rows from multiple tables in MySQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related