user7195201 user7195201 - 17 days ago 5
MySQL Question

MySQL Query to look up values in a second table

I'm a MySQL newby and for some reason this solution is eluding me. I have two tables:

users
|--------------------------------------------------|
| user_id | user_name | email |
|--------------------------------------------------|
| 1 | user1 | user1@somedomain.com |
| 2 | user2 | user2@someotherdomain.com |
| 3 | user3 | user3@yetanotherdomain.com |
|--------------------------------------------------|

user_meta
|---------------------------------|
| user_id | meta_key | meta_value |
|---------------------------------|
| 1 | first_name | john |
| 1 | last_name | doe |
| 2 | first_name | joe |
| 2 | last_name | schmoe |
| 3 | first_name | harry |
| 3 | last_name | pickle |
|---------------------------------|


I want a single query that produces this output:

|---------------------------------------------------------------|
| user_id | first_name | last_name | email |
|----------------------------------|----------------------------|
| 1 | john | doe | user1@somedomain.com |
| 2 | joe | schmoe | user2@someotherdomain.com |
| 3 | harry | pickle | user3@yetanotherdomain.com |
|----------------------------------|----------------------------|


I've tried several flavors of joins and tried subqueries but there must be some simple method that I'm missing. I can get the first name or the last name but not both in the output. Any help would be appreciated.

Thanks

Answer

I used left outer joins here because there is no guarantee that a first_name or last_name key will exist for a given user_id. In those instances, we will still show the user_id and email, but there will be NULL in place for the name.

SELECT U1.user_id, M1.first_name, M2.last_name, U1.email
FROM users U1
LEFT OUTER JOIN user_meta M1 ON M1.user_id = U1.user_id AND M1.meta_key = 'first_name'
LEFT OUTER JOIN user_meta M2 ON M2.user_id = U1.user_id AND M2.meta_key = 'last_name'