mysql group_concat

Question

I have two tables.

products

id  title      image_ids
---------------------
1   myproduct  1,2,3

images

id  title     file_name
-------------------------
1   myimage   myimage.jpg
2   myimage2  myimage2.jpg
3   myimage3  myimage3.jpg

I want to query so that the names of the images are concatenated into a single field for each product reference.

This query doesn't work

   SELECT products.title,
          products.image_ids,
          GROUP_CONCAT(images.file_name)
     FROM products
LEFT JOIN images ON images.id IN (products.image_ids)
    WHERE products.id = 1
    GROUP BY products.id

This one does:

   SELECT products.title,
          products.image_ids,
          GROUP_CONCAT(images.file_name)
     FROM products
LEFT JOIN images ON images.id IN (1,2,3)
    WHERE products.id = 1
    GROUP BY products.id

And produces the desired results

title       image_ids  file_names
--------------------------------------------------------------
myproduct   1,2,3      myimage.jpg,myimage2.jpg,myimage3.jpg 

Why doesn't the first query work when it is asking the same thing as the second query and how can I make it work.

Answer 1

IN does not work for comma-separated lists of values.

Basically, you are not comparing integers, you are comparing strings:

SELECT  1 IN (1, 2, 3) -- True
SELECT  1 IN ('1, 2, 3') -- False ('1' <> '1, 2, 3')

Use FIND_IN_SET instead:

SELECT  products.title,products.image_ids, GROUP_CONCAT(images.file_name)
FROM    products
LEFT JOIN
        images
ON      FIND_IN_SET(images.id, products.image_ids)
WHERE   products.id = 1
GROUP BY
        products.id

This, however, is not the best solution performance-wise since FIND_IN_SET is non-sargable. It will require a full table scan on images.

If you have some reasonable limit on the number of values in products.image_ids (say, no more than 5 images per product), you can use this query instead:

SELECT  products.title,products.image_ids, GROUP_CONCAT(images.file_name)
FROM    (
        SELECT  1 AS n
        UNION ALL
        SELECT  2 AS n
        UNION ALL
        SELECT  3 AS n
        UNION ALL
        SELECT  4 AS n
        UNION ALL
        SELECT  5 AS n
        ) q
CROSS JOIN
        products
LEFT JOIN
        images
ON      SUBSTRING_INDEX(SUBSTRING_INDEX(image_ids, ',', n), ',', 1)
WHERE   products.id = 1
        AND SUBSTRING_INDEX(image_ids, ',', n) <> SUBSTRING_INDEX(image_ids, ',', n - 1)
GROUP BY
        products.id