A. Lau A. Lau - 3 months ago 8
MySQL Question

Using in from a group_concat

So I have two tables, one holding all users, and the other holding users with their corresponding groups. What I'm trying to do is to get all the user ids in the second table to use that to get all the information I need from the first table. My explanation is a bit messy, but I think the code below should be clear enough

SELECT
*
FROM
USER
WHERE
id IN (
SELECT
group_concat(userid)
FROM
user_membership
WHERE
groupid = 45
);


However, what returns is the empty set. When I replace the 'in' with an '=' it returns a single entry (as expected). What's wrong with my syntax? Thanks.

Answer

You don't need group_concat() here as it concatenates every userid into a string column and you need to compare each user.id with a single user_membership.userid value, not an entire set. It's an overkill to perform that operation.

Simple select would suffice. However, it will probably be more efficient to rewrite your query using EXISTS:

select *
from user u
where exists (
  select 1
  from user_membership um
  where u.id = um.userid
    and groupid = 45
  )

If you really insist on the IN clause then correct query would look:

select *
from user
where id in (
  select userid
  from user_membership
  where groupid = 45
  )
Comments