MYSQL request from two tables with FIND_IN_SET

4
votes

I have two tables (MySQL):

  1. table_products: // cid (text) - comma separated categories list // id (int11)

    id   cid
1    1,2,3        
2    5,21,8
3    2,4,15

  2. table_categories

    id   name   parent        
1    Cat1   0      
2    Cat2   0
3    Cat3   0
4    Cat4   0

This is my request:

SELECT `id`, `name`, `parent`
FROM `table_categories`
ORDER BY `parent` ASC, `id` ASC;

How can I perform the request above and also get the number of products in each category (from table_products). May be using (FIND_IN_SET).

1
mysqlcountrequest

1 Answers

15
votes

If every category is present in table_categories, then you could use this:

SELECT table_categories.name, count(table_products.id)
FROM
  table_categories LEFT JOIN products
  ON FIND_IN_SET(table_categories.id, products.cid)
GROUP BY table_categories.name

If table_categories.id is present in products.cid then FIND_IN_SET will return a non-zero value, and the join will succeed.