MYSQL request from two tables with FIND_IN_SET

Question

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).

Answer 1

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.