Search code examples
mysqlgroup-concat

Mysql GROUP_CONCAT using a GROUP_CONCAT in the query's IN()

I have a query:

SELECT GROUP_CONCAT(stores.store_name)
FROM stores
WHERE stores.id IN
(

  SELECT
  GROUP_CONCAT(users_x_stores.store_id) as stores_list
  FROM users_x_stores
  WHERE users_x_stores.user_id = 4

 );

The subquery, when run alone, returns a group_concat of 3 results - 14,4,8.

There are corresponding rows for the IDs 14,4,8 - but the overall query only returns one store name.

If I change the subquery to simply 14,4,8 the overall query works and a concatenation of 3 store names is returned.

Can anyone tell me what I am doing incorrectly here?

Thanks.

Solution

  • Try this

    SELECT GROUP_CONCAT(stores.store_name)
FROM stores
WHERE stores.id IN
(
SELECT
users_x_stores.store_id as stores_list
FROM users_x_stores
WHERE users_x_stores.user_id = 4
);