Subquery that returns multiple rows

[>ssp-cdr<]

I have a question concerning using a subquery in a SELECT statement. The problem I am having occures because a subquery is only allowed to return one row, but my subquery may return several rows.

In my database there is a user table, a usergroup table and, since a user can be in multiple usergroups, a user-usergroup table to link the two.

This is they kind of query I want to runSELECT
username,
(SELECT usergroupid FROM `user-usergroup` WHERE userid = 150) AS usergroupids

FROM user WHERE userid = 150

What I would like this query to return is the username and a comma separated list of usergroupids.

Because a subquery is only allowed to return one row, I am wondering if there is some way to compact multiple rows into a string (similar to what can be done with PHP's implode() function)?

Thanks! :)

[Fumigator]

You can do a JOIN instead of a subquery; but that returns multiple rows. That is usually easy to handle if you are working within a programming language. But MySQL also provides a function GROUP_CONCAT, which returns a comma-delimited list of values in a single row. You may be able to take advantage of this function.

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

[>ssp-cdr<]

Yes, thank you. This will work.

SELECT
user.username,
GROUP_CONCAT(`user-usergroup`.usergroupid) AS usergroupids

FROM user
LEFT JOIN `user-usergroup` ON `user-usergroup`.userid = user.userid

WHERE user.userid = 150

GROUP BY user.userid