Re: mySQL query result from multiple tables to PHP

[Felix Geerinckx]

stodge wrote:

> Is there any way using joins to retrieve the following result as single query:
>
> user_id = 1
> username = mike
> group_name = array(admin, user)


SELECT
u.user_id,
u.username,
GROUP_CONCAT(g.group_name) AS grouplist
FROM users u
JOIN user_groups ug ON ug.user_id = u.user_id
JOIN groups g ON g.group_id = ug.group_id
WHERE
u.user_id = 1

If a user can belong to no groups, use LEFT JOINs instead.
You will have to parse the grouplist in your application program, it comes as a string e.g. "admin,user"

--
felix
Please use BBCode to format your messages in this forum.