MySQL query joining issue while using FIND_IN_SET and GROUP_CONCAT

Question

So I have a product/categories/brands structure where a products categories are identified by a column containing a comma separated list of category IDs, i.e. 101,105,108, as well as a brand ID column.

I'm trying to get a list of all the products, replace the category IDs with a comma separated list of category names, and also the brand name.

I have the following query that works:

SELECT 
    productid AS product_id,
    prodname AS name,
    prodcode AS code,
    proddesc AS description,
    prodprice AS price,
    GROUP_CONCAT(c.catname)
FROM
    products p,
    categories c
WHERE
    FIND_IN_SET(c.categoryid, p.prodcatids)
GROUP BY p.productid

However when I try and left join as follows to also get the brand name, it breaks and says that column p.prodbrandid doesn't exist (it does).

SELECT 
    productid AS product_id,
    prodname AS name,
    prodcode AS code,
    proddesc AS description,
    prodprice AS price,
    b.brandname AS brand,
    GROUP_CONCAT(c.catname)
FROM
    products p,
    categories c
        LEFT JOIN
    brands b ON p.prodbrandid = b.brandid
WHERE
    FIND_IN_SET(c.categoryid, p.prodcatids)
GROUP BY p.productid

Any pointers to what I'm missing would be greatly appreciated!

Don't mix explicit and implicit join. Actually always use modern, explicit `JOIN` syntax! — jarlh, Jan 14 '19 at 20:57
Explicit JOINs are evaluated before comma separated ones. So your LEFT JOIN has no access to table p's columns. — jarlh, Jan 14 '19 at 20:58
Also, explicit JOIN chains are evaluated from left to right. — jarlh, Jan 14 '19 at 21:09
And don't store lists of ids in a string! That suggests a broken data model. You should have a `productCategories` table. — Gordon Linoff, Jan 14 '19 at 22:29
@GordonLinoff completely agree, however working with a large system that's already in place which cannot be changed! :( — HirtLocker128, Jan 15 '19 at 12:59

Caius Jard · Accepted Answer · 2019-01-14T21:18:19.073

From the advice in the comments:

SELECT 
    p.productid AS product_id,
    p.prodname AS name,
    p.prodcode AS code,
    p.proddesc AS description,
    p.prodprice AS price,
    b.brandname AS brand,
    GROUP_CONCAT(c.catname)
FROM
    products p
    INNER JOIN categories c on FIND_IN_SET(c.categoryid, p.prodcatids) > 0
    LEFT JOIN brands b ON p.prodbrandid = b.brandid        
GROUP BY p.productid

It's not ideal to store data as comma separated lists though; this really should be split out to an additional table that breaks down the many:many relationship between product and category (multiple products can have multiple categories) into two 1:many relationships (a productcategories table, that has a productid,categoryid pair)

Consider something like this as a one time op:

CREATE TABLE ProductCategories(ProductId INT, CategoryId INT)

INSERT INTO ProductCategories
  SELECT 
  p.productid, c.categoryid
FROM
  products p
  INNER JOIN categories c on FIND_IN_SET(c.categoryid, p.prodcatids) > 0

Then use it going forwards, and drop the categories column

Thanks! I agree about storing data as comma separated list, however I'm working with a large (clearly not well designed) system that's already in place which cannot be changed. Maybe will try to push for this structure in the future — HirtLocker128, Jan 15 '19 at 13:01

MySQL query joining issue while using FIND_IN_SET and GROUP_CONCAT

1 Answers1