0

Table 1 - users example

ID   usr_login  user_email        display_name
1    john       [email protected]    John Jones    
2    steve      [email protected]     Steve Jobs    
3    tom        [email protected]   Tom Thumb

Table 2 - usermeta

umeta_id  user_id   meta_key    meta_value    
1         1         phone       8005551212    
2         1         email       [email protected]    
3         1         b_date      12/25    
20        2         phone       2025554567    
21        2         email       [email protected]    
22        2         b_date      11/01    
40        3         phone       9095559876    
41        3         email       [email protected]    
42        3         b_date      01/30

I am trying to display the fields on a webpage in this format

User1-DisplayName     User1-eMailAddress     User1-Phone     User1-Bdate
User2-DisplayName     User2-eMailAddress     User2-Phone     User2-Bdate
User3-DisplayName     User2-eMailAddress     User3-Phone     User3-Bdate

I can get data to display from each of the tables. But, I have not been able to get data to show from both tables together. I know enough to know that I should use a join, but can seem to get the select statement correct.

Thanks in advance, Kevin

Improve this question

1 Answer 1

Reset to default
1

Since you say you can already get data from the tables individually, I'm assuming all you need is the appropriate SQL query. To get the data you want, you need to join the users table to the usermeta table twice, once to get the phone value and once to get the b_date value. In each case the join condition is on the user_id value and the appropriate meta_key value:

SELECT u.display_name,
       u.user_email,
       p.meta_value AS phone,
       b.meta_value AS bdate
FROM users u
JOIN usermeta p ON p.user_id = u.ID AND p.meta_key = 'phone'
JOIN usermeta b ON b.user_id = u.ID AND b.meta_key = 'b_date'
ORDER BY u.ID

Output:

display_name    user_email          phone       bdate
John Jones      [email protected]      8005551212  12/25
Steve Jobs      [email protected]       2025554567  11/01
Tom Thumb       [email protected]     9095559876  01/30

Note I've assumed you get the user_email value from the users table; if you want to get that from usermeta as well, you would join a third time:

SELECT u.display_name,
       e.meta_value AS user_email,
       p.meta_value AS phone,
       b.meta_value AS bdate
FROM users u
JOIN usermeta e ON e.user_id = u.ID AND e.meta_key = 'email'
JOIN usermeta p ON p.user_id = u.ID AND p.meta_key = 'phone'
JOIN usermeta b ON b.user_id = u.ID AND b.meta_key = 'b_date'
ORDER BY u.ID

The output is the same for this query.

Demo on db-fiddle

Note also that I've assumed you have email, phone and b_date records in the usermeta table for each user. If you don't, you will need to change the JOINs into LEFT JOINs, and use a COALESCE to convert any resultant NULL values into empty strings e.g.

COALESCE(p.meta_value, '') AS phone

For example:

SELECT u.display_name,
       COALESCE(e.meta_value, '') AS user_email,
       COALESCE(p.meta_value, '') AS phone,
       COALESCE(b.meta_value, '') AS bdate
FROM users u
LEFT JOIN usermeta e ON e.user_id = u.ID AND e.meta_key = 'email'
LEFT JOIN usermeta p ON p.user_id = u.ID AND p.meta_key = 'phone'
LEFT JOIN usermeta b ON b.user_id = u.ID AND b.meta_key = 'b_date'
ORDER BY u.ID

Demo on db-fiddle

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

12 Comments

Nick, thank you for your quick response! Not sure why that code returned only 6 records when I ran the query on the database. It returned records 3, 4, 8, 15, 17, 23, 29 from the users table. All records returned have user_email. Five of the records returned have phone & b_date. I tried to get it to return ID from the users table, for troubleshooting, with no success. I also tried to adapt this code to PHP, since the end goal is to display this on a web page from a php file.
I tried eliminating the lines for b_date and it returned 28 lines. Still missing the last 3 users records entered. I eliminated the phone lines and it returned the same 28 records. I did get it to display the ID column from the users table.
I did find that the only users_id that have the b-date field are those 6 that are returned. How do you get it to return all records from users and show the columns from usermeta whether they have the key in meta_key for the user_id?
@KevinW I've assumed you have email, phone and b_date records in the usermeta table for each user. If you don't, you will need to change the JOINs into LEFT JOINs, and use a COALESCE to convert the resultant NULL values into empty strings e.g. COALESCE(p.meta_value, '') AS phone. See for example db-fiddle.com/f/qJourHVhcofbVaWEk4DCEP/3
@KevinW I've updated the answer to reflect my prior comment
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.