MySQL select group concat?

Question

我正在尝试从两个表中进行选择。我有两张桌子：

select * from igw41_users;
+-----+------------+----------+-----------------------------+--------------------------------------------------------------+-------+-----------+---------------------+---------------------+------------+--------------------------------------------------------------------------------------------------------------------------------+---------------------+------------+--------+------+--------------+
| id  | name       | username | email                       | password                                                     | block | sendEmail | registerDate        | lastvisitDate       | activation | params                                                                                                                         | lastResetTime       | resetCount | otpKey | otep | requireReset |
17      John Doe     AAAAAA   nothing else is needed from this table.
select * from igw41_user_profiles;
+---------+----------------------+-----------------------+----------+
| user_id | profile_key          | profile_value         | ordering |
17      profile.account_type    "2"                      4
17      profile.postal_code     "75055"                  1

我需要获取id(来自igw41_users(、用户名igw41_users、postal_code(来自igw 41_user_profiles(和account_type。但是，我需要的信息是每个igw41_user_profiles.user_id的profile_values中与igw41_users.id匹配的信息。

select id, username, GROUP_CONCAT(profile_value SEPARATOR ',') 
from igw41_users 
left join igw41_user_profiles on igw41_users.id = igw41_user_profiles.user_id and igw41_user_profiles.profile_key = 'profile.account_type' 
group by username;

这给了我account_type，但我不知道如何获得postal_code。如果我进行了正确的联接，它会给我：错误1066(42000(：不是唯一的表/别名：'igw41_user_profiles'

这就是我试图获得的两个配置文件值：

select id, username, GROUP_CONCAT(profile_value SEPARATOR ',') 
from igw41_users 
left join igw41_user_profiles on igw41_users.id = igw41_user_profiles.user_id and igw41_user_profiles.profile_key = 'profile.account_type' 
right join igw41_user_profiles on igw41_users.id = igw41_user_profiles.user_id and igw41_user_profiles.profile_key = 'profile.postal_code' 
group by username;

提前谢谢。

Answer 1

take和igw41_user_profile.profile_key='profile.account_type'退出您的第一个查询

如果你想在中测试两者的使用

and igw41_user_profiles.profile_key in('profile.account_type','profile.postal_code')

Answer 2

通常情况下，当您需要多次加入同一个表时，每次加入都需要使用别名：

select id, username, GROUP_CONCAT(profile_value SEPARATOR ',') 
from igw41_users 
left join igw41_user_profiles p1
on igw41_users.id = p1.user_id and 
p1.profile_key = 'profile.account_type' 
right join igw41_user_profiles p2
on igw41_users.id = p2.user_id and
p2.profile_key = 'profile.postal_code' 
group by username;

在这种情况下，第二个连接是多余的：

select id, username, GROUP_CONCAT(profile_value SEPARATOR ',') 
from igw41_users 
left join igw41_user_profiles p1 on igw41_users.id = p1.user_id
where
p1.profile_key = 'profile.account_type' OR 
p1.profile_key = 'profile.postal_code'
group by username;