Mixing
select GROUP_CONCAT in mysql
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I have two tables:
table1:
id email
1 john@gmail.com
2 doe@gmail.com
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] => john@gmail.com
[userid] => 1
[key] => firstname,phone
[value] => john,112233
)
array(
[id] => 2
[email] => doe@gmail.com
[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] => john@gmail.com
[userid] => 1
[key] => firstname
[value] => john
)
But I want this:
array(
[id] => 1
[email] => john@gmail.com
[userid] => 1
[key] => firstname,phone
[value] => john,112233
)
There any way to do this? thank for any help!
php mysql group-concat
edited Jan 3 at 4:22
Ahmed_Ali
1,40221635
asked Jan 3 at 3:33
Hữu Công HoàngHữu Công Hoàng
2318
add a comment |
0
I have two tables:
table1:
id email
1 john@gmail.com
2 doe@gmail.com
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] => john@gmail.com
[userid] => 1
[key] => firstname,phone
[value] => john,112233
)
array(
[id] => 2
[email] => doe@gmail.com
[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] => john@gmail.com
[userid] => 1
[key] => firstname
[value] => john
)
But I want this:
array(
[id] => 1
[email] => john@gmail.com
[userid] => 1
[key] => firstname,phone
[value] => john,112233
)
There any way to do this? thank for any help!
php mysql group-concat
edited Jan 3 at 4:22
Ahmed_Ali
1,40221635
asked Jan 3 at 3:33
Hữu Công HoàngHữu Công Hoàng
2318
add a comment |
0
0
0
1
I have two tables:
table1:
id email
1 john@gmail.com
2 doe@gmail.com
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] => john@gmail.com
[userid] => 1
[key] => firstname,phone
[value] => john,112233
)
array(
[id] => 2
[email] => doe@gmail.com
[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] => john@gmail.com
[userid] => 1
[key] => firstname
[value] => john
)
But I want this:
array(
[id] => 1
[email] => john@gmail.com
[userid] => 1
[key] => firstname,phone
[value] => john,112233
)
There any way to do this? thank for any help!
php mysql group-concat
edited Jan 3 at 4:22
Ahmed_Ali
1,40221635
asked Jan 3 at 3:33
Hữu Công HoàngHữu Công Hoàng
2318
I have two tables:
table1:
id email
1 john@gmail.com
2 doe@gmail.com
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] => john@gmail.com
[userid] => 1
[key] => firstname,phone
[value] => john,112233
)
array(
[id] => 2
[email] => doe@gmail.com
[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] => john@gmail.com
[userid] => 1
[key] => firstname
[value] => john
)
But I want this:
array(
[id] => 1
[email] => john@gmail.com
[userid] => 1
[key] => firstname,phone
[value] => john,112233
)
There any way to do this? thank for any help!
php mysql group-concat
php mysql group-concat
edited Jan 3 at 4:22
Ahmed_Ali
1,40221635
asked Jan 3 at 3:33
Hữu Công HoàngHữu Công Hoàng
2318
edited Jan 3 at 4:22
Ahmed_Ali
1,40221635
asked Jan 3 at 3:33
Hữu Công HoàngHữu Công Hoàng
2318
edited Jan 3 at 4:22
Ahmed_Ali
1,40221635
edited Jan 3 at 4:22
Ahmed_Ali
1,40221635
edited Jan 3 at 4:22
Ahmed_Ali
1,40221635
1,40221635
asked Jan 3 at 3:33
Hữu Công HoàngHữu Công Hoàng
2318
asked Jan 3 at 3:33
Hữu Công HoàngHữu Công Hoàng
2318
asked Jan 3 at 3:33
Hữu Công HoàngHữu Công Hoàng
2318
2318
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
3
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.
answered Jan 3 at 3:41
Thorsten KettnerThorsten Kettner
53k32643
1
Just one suggestion:SELECT *afterGROUP BYclause is not recommended.
– Shidersz
Jan 3 at 4:00
@Shidersz: Thank you, you are right.select a.*is okay and valid SQL, becausea.idis supposedly the table's key, soa.*is functionally dependent ongroup by a.id, butb.*isn't and can lead to arbitrarily picked values. I've edited my answer accordingly and removedb.*from the select list.
– Thorsten Kettner
Jan 3 at 9:00
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54015996%2fselect-group-concat-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
3
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.
answered Jan 3 at 3:41
Thorsten KettnerThorsten Kettner
53k32643
1
Just one suggestion:SELECT *afterGROUP BYclause is not recommended.
– Shidersz
Jan 3 at 4:00
@Shidersz: Thank you, you are right.select a.*is okay and valid SQL, becausea.idis supposedly the table's key, soa.*is functionally dependent ongroup by a.id, butb.*isn't and can lead to arbitrarily picked values. I've edited my answer accordingly and removedb.*from the select list.
– Thorsten Kettner
Jan 3 at 9:00
add a comment |
3
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.
answered Jan 3 at 3:41
Thorsten KettnerThorsten Kettner
53k32643
1
Just one suggestion:SELECT *afterGROUP BYclause is not recommended.
– Shidersz
Jan 3 at 4:00
@Shidersz: Thank you, you are right.select a.*is okay and valid SQL, becausea.idis supposedly the table's key, soa.*is functionally dependent ongroup by a.id, butb.*isn't and can lead to arbitrarily picked values. I've edited my answer accordingly and removedb.*from the select list.
– Thorsten Kettner
Jan 3 at 9:00
add a comment |
3
3
3
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.
answered Jan 3 at 3:41
Thorsten KettnerThorsten Kettner
53k32643
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.
answered Jan 3 at 3:41
Thorsten KettnerThorsten Kettner
53k32643
edited Jan 3 at 9:01
answered Jan 3 at 3:41
Thorsten KettnerThorsten Kettner
53k32643
answered Jan 3 at 3:41
Thorsten KettnerThorsten Kettner
53k32643
answered Jan 3 at 3:41
Thorsten KettnerThorsten Kettner
53k32643
53k32643
1
Just one suggestion:SELECT *afterGROUP BYclause is not recommended.
– Shidersz
Jan 3 at 4:00
@Shidersz: Thank you, you are right.select a.*is okay and valid SQL, becausea.idis supposedly the table's key, soa.*is functionally dependent ongroup by a.id, butb.*isn't and can lead to arbitrarily picked values. I've edited my answer accordingly and removedb.*from the select list.
– Thorsten Kettner
Jan 3 at 9:00
add a comment |
1
Just one suggestion:SELECT *afterGROUP BYclause is not recommended.
– Shidersz
Jan 3 at 4:00
@Shidersz: Thank you, you are right.select a.*is okay and valid SQL, becausea.idis supposedly the table's key, soa.*is functionally dependent ongroup by a.id, butb.*isn't and can lead to arbitrarily picked values. I've edited my answer accordingly and removedb.*from the select list.
– Thorsten Kettner
Jan 3 at 9:00
1
1
Just one suggestion:
SELECT * after GROUP BY clause is not recommended.– Shidersz
Jan 3 at 4:00
Just one suggestion:
SELECT * after GROUP BY clause is not recommended.– Shidersz
Jan 3 at 4:00
@Shidersz: Thank you, you are right.
select a.* is okay and valid SQL, because a.id is supposedly the table's key, so a.* is functionally dependent on group by a.id, but b.* isn't and can lead to arbitrarily picked values. I've edited my answer accordingly and removed b.* from the select list.– Thorsten Kettner
Jan 3 at 9:00
@Shidersz: Thank you, you are right.
select a.* is okay and valid SQL, because a.id is supposedly the table's key, so a.* is functionally dependent on group by a.id, but b.* isn't and can lead to arbitrarily picked values. I've edited my answer accordingly and removed b.* from the select list.– Thorsten Kettner
Jan 3 at 9:00
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54015996%2fselect-group-concat-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
