Hữu Công Hoàng
Reputation: 33
select GROUP_CONCAT in mysql
I have two tables:
table1:
id email
1 [email protected]
2 [email protected]
table2:
userid key value
1 firstname john
1 phone 112233
2 firstname doe
2 phone 223344
This is mysql query without condition:
SELECT a.*,
b.*,
GROUP_CONCAT(b.key),
GROUP_CONCAT(b.value)
FROM table1 a
LEFT JOIN table2 b ON a.id = b.userid
This is result:
array(
[id] => 1
[email] => [email protected]
[userid] => 1
[key] => firstname,phone
[value] => john,112233
)
array(
[id] => 2
[email] => [email protected]
[userid] => 2
[key] => firstname,phone
[value] => doe,223344
)
This is mysql query with condition:
SELECT a.*,
b.*,
GROUP_CONCAT(b.key),
GROUP_CONCAT(b.value)
FROM table1 a
LEFT JOIN table2 b ON a.id = b.userid
WHERE b.key = "firstname"
AND b.value LIKE '%jo%'
And this is result:
array(
[id] => 1
[email] => [email protected]
[userid] => 1
[key] => firstname
[value] => john
)
But I want this:
array(
[id] => 1
[email] => [email protected]
[userid] => 1
[key] => firstname,phone
[value] => john,112233
)
There any way to do this? thank for any help!
Upvotes: 0
Views: 277
Answers (1)
Thorsten Kettner
Reputation: 95101
Your queries are lacking the GROUP BY clause to get a row per user. Then use a HAVING clause to make sure the aggregated row includes a firstname '%jo%':
SELECT a.*,
GROUP_CONCAT(b.key),
GROUP_CONCAT(b.value)
FROM table1 a
LEFT JOIN table2 b ON a.id = b.userid
GROUP BY a.id
HAVING sum(b.key = 'firstname'
AND b.value LIKE '%jo%') > 0;
true results in 1, false in 0 in MySQL. So checking whether the sum is greater than zero means checking whether the condition is true at least once.
Upvotes: 3