S.M. Nat S.M. Nat - 7 months ago 21
SQL Question

Mysql multi join with count

have 3 tables

product_tags

product_id | tag
___________________
50 | new
50 | blac
66 | new
50 | green
111 | new
111 | white


products_to_categories

product_id | category_id
____________________
50 | 69
50 | 68
111 | 40
111 | 70


categories

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

SELECT tag
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

tags
_____
new (2)
white (1)

Answer

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.