CODEWITHSUNDEEP
mysqlinner-joincorrelated-subquery
xvilo
xvilo

Reputation: 273

SQL inner join combine to one row

With this SQL Query I get a result per meta value, so in this case 2 results at a time. What should happen is that every meta_key is getting it's own column (user_phone and full_name) with meta_key in the row. 

SELECT * 
FROM invoice_users
INNER JOIN invoice_usermeta 
    on invoice_users.id = invoice_usermeta.user_id 
WHERE meta_key='full_name' OR meta_key='user_phone';

I tried a GROUP BY but that was not right, and I couldn't understand subqueries...

Database scheme of invoice_users

ID | user_login | user_register
--------------------------------
 0    username    [Timestamp]
 1     user2      [Timestamp]
 2     user3      [Timestamp]

Database scheme of invoice_usermeta

ID | user_id |  meta_key | meta_value
--------------------------------------
 0      0      user_phone   0334234
 1      0      full_name    John Doe
 2      1      user_phone   3453455
 3      1      full_name    Jane Doe
 4      2      user_phone   78678678
 5      3      full_name    John Smith

The results I am getting:

ID  |  user_login | user_registered |  meta_key | meta_value
 0      username      [Timestamp]     full_name    John Doe
 0      username      [Timestamp]     user_phone   0334234
 1      username      [Timestamp]     full_name    Jane Doe
 1      username      [Timestamp]     user_phone   3453455

etc..

What I want

ID  |  user_login | user_registered | user_id |full_name | user_phone
 0      username      [Timestamp]        0      John Doe    0334234
 1      username      [Timestamp]        1      Jane Doe    3453455

etc

Upvotes: 0

Views: 48

Answers (1)

Brian Rudolph
Brian Rudolph

Reputation: 6318

The info you provided is a bit sparse, but I will take a stab at it. What you likely want to do it perform your join twice, like this:

SELECT u.*, m1.meta_value, m2.meta_value
FROM invoice_users u
LEFT OUTER JOIN invoice_usermeta m1
    on u.id = m1.user_id 
    and m1.meta_key='user_phone'
LEFT OUTER JOIN invoice_usermeta m2
    on u.id = m2.user_id 
    and m2.meta_key='full_name'

If this isn't sufficient because you have many meta values, you would want to investigate whatever the MySQL equivalent is to Sql Server PIVOT(could very well be PIVOT, I just don't know enough about MySQL)

Upvotes: 2

Related Questions