Focus on IT Recommend

Home > mysql - SQL inner join combine to one row

mysql - 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.

FROM invoice_users
INNER JOIN invoice_usermeta 
    on = 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


What I want

Recommend:sql - MySQL correlated subquery in JOIN syntax

= However, MySQL (5.0.45) reports "Unknown column '' in 'where clause'". Is this type of query possible The inner query is pivoting rows to columns using a GROUP BY. This could be entirely be performed in the ou

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


mysql inner-join correlated-subquery
edited Dec 29 '15 at 18:36 Tab Alleman 15.1k 4 19 35 asked Dec 29 '15 at 18:20 xvilo 16 2 7 1   could you add the result you're getting and what you expect? The database schema would also be helpful here. –  davejal Dec 29 '15 at 18:23      Also, is this for MySQL or SQL Server? Theyr'e different products. –  Dan Field Dec 29 '15 at 18:31      Its for mysql @DanField –  xvilo Dec 29 '15 at 18:35      Updated it @davejal –  xvilo Dec 29 '15 at 18:35


1 Answers


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 = m1.user_id 
    and m1.meta_key='user_phone'
LEFT OUTER JOIN invoice_usermeta m2
    on = 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)

edited Dec 29 '15 at 18:39 answered Dec 29 '15 at 18:25 Brian Rudolph 4,688 1 17 16      Updated my main comment. Sorry for the little info, als hard to others about that haha –  xvilo Dec 29 '15 at 18:35      does this work for you'? –  davejal Dec 29 '15 at 18:37      I get Unknown column 'm1.value' in 'field list' do i have to change these? –  xvilo Dec 29 '15 at 18:38      Sorry, but i am very new to SQL, if i ask any weird questions @davejal –  xvilo Dec 29 '15 at 18:38 1   yes, it was just an example, since I didn't know what your schema looked like. I edited it. –  Brian Rudolph Dec 29 '15 at 18:38  |  show more comments

Recommend:sql - mysql limit with inner join and subquery

) AS `count` FROM saturday_combinations INNER JOIN (SELECT ONE AS valFROM saturday_combinations WHERE ONE IS NOT NULL UNION SELECT TWO AS valFROM saturday_combinationsWHERE TWO IS NOT NULL UNION SELECT THREE AS valFROM sat


------splitte line----------------------------