group_concat mysql subquery only returns one item

Question

I have two tables, scoreTable and users. I am trying to group by hash on scoreTable but selecting all the user names that have that hash. scoreTable only has the ids

SELECT st.score,
GROUP_CONCAT(st.uid) as userList
FROM scoreTable st
GROUP BY hash

For the above query I get the list of user Ids in userList: '1,2,3,4'

What I would love to get is the actual "names" instead of the ids - the names are on another table (users)

SELECT st.score,
(SELECT group_concat(d.name) from users d d where d.uid = st.uid))
FROM scoreTable st
GROUP BY hash

But for some reason this only displays ONE user (the user with the first id).

Put the GROUP BY in your subquery

Mihai
– Mihai

2013-12-20 20:06:31 +00:00
Commented Dec 20, 2013 at 20:06 — Mihai
– Mihai, Commented Dec 20, 2013 at 20:06

Mureinik · Accepted Answer · 2013-12-20 20:07:31Z

2

Just join on the users table:

SELECT   st.score, group_concat(d.name)
FROM     scoreTable st
JOIN     users d ON d.uid = st.uid
GROUP BY hash

answered Dec 20, 2013 at 20:07

Mureinik

316k54 gold badges401 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mureinik Over a year ago

@Cez The first query in the OP assumes this, I just carried on with this assumption

K2xL Over a year ago

worked great thanks, now to figure out how to group_concat the name and id in a json format that won't break if name has double quotes...

Collectives™ on Stack Overflow

group_concat mysql subquery only returns one item

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related