一个查询来获取单元格位于不同表中的记录

Table users:

user_id     user_username   user_password
-----------------------------------------
1           tomasr          NULL

Table useritems:

f_user_id   f_moduleItem_id     userItem_value
--------------------------------------------------
1           1                   John
1           2                   Wayne

Table modules:

module_id   module_systemTitle  module_title
------------------------------------------------
1           users               Users

Table moduleitems:

moduleItem_id   f_module_id     moduleItem_title    moduleItem_order
----------------------------------------------------------------------------
1               1               Firstname           1
2               1               Lastname            2

Hello everybody, how to get list of users with data from useritems table like one row with one user. That is mean number rows eqal number users.

各位大家好,如何从useritems表中获取数据的用户列表,如同一个用户的一行。这是平均数行eqal数用户。

Desire result is:

欲望的结果是:

user_id user_username   firstname    lastname
------------------------------------------------------------------------------

1       tomasr          John         Wayne

2 个解决方案

#1

You will want to use INNER JOIN to join two tables together. The link will explain it better but something like this:

您将希望使用INNER JOIN将两个表连接在一起。该链接将更好地解释它,但这样的事情:

SELECT u.user_username, i.userItem_value 
FROM users u 
INNER JOIN userItems i ON u.user_id=i.f_user_id;

What this will do is return all rows where a match is found but remember that if there is not a row in the userItems that matches the users table row then the users row will not be included in the results. For that you will want to look at LEFT JOINS and use IFNULL(something like that).

这样做会返回找到匹配项的所有行,但请记住,如果userItems中没有与users表行匹配的行,则用户行将不会包含在结果中。为此你需要看看LEFT JOINS并使用IFNULL(类似的东西)。

#2

SELECT  a.user_ID,
        a.user_username,
        MAX(CASE WHEN c.moduleItem_title = 'Firstname' THEN b.userItem_value ELSE NULL END) `Firstname`,
        MAX(CASE WHEN c.moduleItem_title = 'Lastname' THEN b.userItem_value ELSE NULL END) `Lastname`
FROM    users a
        INNER JOIN useritems b
            On a.user_ID = b.f_user_id
        INNER JOIN moduleitems c
            ON b.f_moduleItem_id = c.moduleItem_id
GROUP   BY a.user_ID, a.user_username

• SQLFiddle Demo