Search code examples
mysqlsqlselectgroup-bygroup-concat

Use subquery in WHERE caluse in mysql

I have two tables as user and userGroup related such that each user can be assigned to one or more userGroup. The user table contains a foreign key as group_id when contains comma separated values of the userGroup id. I want to fetch the user details and the comma separated group_name he is related to in a single query. Is there any method I can achieve this in SQL? The fields of user table are: (id, uname, group_id) userGroup: (id, group_name)

What I want is I get uname, group_name, user.id, group.id in a single SQL query.

I have tried the following query but it gives me error

SELECT t1.uname, group_concat(t.group_name) FROM user t1
LEFT JOIN user_group t2
ON t1.group_id = t2.id
    WHERE t2.id IN t1.group_id
Solution

  • Try this: 

    SELECT t1.uname, GROUP_CONCAT(t.group_name) AS group_name 
FROM user t1
LEFT JOIN user_group t2 ON FIND_IN_SET(t2.id, t1.group_id)
GROUP BY t1.id;