have 3 tables
product_id | tag
50 | new
50 | blac
66 | new
50 | green
111 | new
111 | white
product_id | category_id
50 | 69
50 | 68
111 | 40
111 | 70
category_id | parent_id (parent category id)
68 | 0
69 | 68
70 | 68
need all tags sorted by popularity (count product) within category 68 and its all subcategories (all categories with parent id 68)
my start query give wrong result
FROM product_tags opd
LEFT JOIN products_to_categories optc ON optc.product_id = opd.product_id
LEFT JOIN categories optx ON optx.parent_id = '68'
WHERE opd.tag <> ''
AND optx.parent_id = '68'
ORDER BY optc.product_id DESC
Result I need
Do this step by step. Use
IN when checking whether a record exists. You want product_ids that are in the set of category_ids 68 and its children:
select tag, count(*) from product_tags where product_id in ( select product_id from products_to_categories where category_id = 68 or category_id in ( select category_id from categories where parent_id = 68 ) ) group by tag order by count(*) desc;
Join on categories was incorrect. It should be:
LEFT JOIN categories optx ON optx.parent_id = optc.category_id
Then to get the correct
count() you should do a
GROUP BY tag:
SELECT CONCAT(opd.tag, ' (', count(*), ')' ) FROM product_tags opd LEFT JOIN products_to_categories optc ON optc.product_id = opd.product_id LEFT JOIN categories optx ON optx.parent_id = optc.category_id WHERE opd.tag <> '' AND optx.parent_id = '68' GROUP BY opd.tag
I think your biggest problem is that you are getting confused with your data and how it pieces together. I have rewritten your query removing your aliases so that you can clearly see what is happening with your joins.
SELECT tag, COUNT(*) AS Num FROM product_tags LEFT JOIN products_to_categories ON product_tags.product_id = product_to_categories.product_id LEFT JOIN categories ON product_to_categories.category_id = categories.category_id WHERE product_tags.tag <> '' AND categories.parent_id = '68' GROUP BY tag ORDER BY Num DESC
I would then use your presentation layer to handle the presentation of your data "New (2)" etc.
Hope this helps.
I think you have a problem with your tables, because you can't do the difference between products id
111, so, I advise you to move the