ItGo.me Focus on IT Recommend

Home > mysql - SQL inner join combine to one row

mysql - SQL inner join combine to one row

2020腾讯云8月秒杀活动,优惠非常大!(领取2860元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1040

2020阿里云最低价产品入口,含代金券(新老用户有优惠),
入口地址https://www.aliyun.com/minisite/goods

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

Recommend:sql - MySQL correlated subquery in JOIN syntax

= outertable.id. However, MySQL (5.0.45) reports "Unknown column 'outertable.id' 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

etc

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
1

---Accepted---Accepted---Accepted---

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)


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

oriUlr:http://stackoverflow.com/questions/34516725/sql-inner-join-combine-to-one-row

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