CODEWITHSUNDEEP
mysqlsqlcountproductcategories
Alex
Alex

Reputation: 1611

Sql categories products count query

Hi I have a difficulty with one query if someone can help would be great

What I m trying to do is to get all counts by selected category. For example If I haven't selected any category the result is going to be like this:

Category-70 ( 2 ) <- Product-57, Product-56
Category-64 ( 2 ) <- Product-57, Product-50
Category-61 ( 1 ) <- Product-56
Category-73 ( 1 ) <- Product-50

So that's easy. I have a query like this one:

http://sqlfiddle.com/#!9/4f188/1

So I would like to pass category ids to my query, and get counts based on this ids, something like this if, I pass category id 70 the result has to be

Category-70 ( 2 ) <- Product-57, Product-56
Category-64 ( 1 ) <- Product-57, [Product-50 is gone because is not in cateogry id 70]
Category-61 ( 0 )
Category-73 ( 0 )

If I pass category id 70 and 64 the result must be

Category-70 ( 1 ) <- Product-57, [Product-56 is gone because is not in category 70 and 64]
Category-64 ( 1 ) <- Product-57
Category-61 ( 0 ) [Product-56 is gone, because is not in category 70 and 64 ]
Category-73 ( 0 ) [Product-50 is gone because is not in category 70 and 64]

or if I give as parameter category id 73 the result must be 

Category-70 ( 0 ) [products are not counted because they are not in 73]
Category-64 ( 1 ) <- Product-50
Category-61 ( 0 ) [products are not counted because they are not in 73]
Category-73 ( 1 ) <- Product-50

Is that even possible :), ty for any help...

Upvotes: 1

Views: 463

Answers (1)

Chris
Chris

Reputation: 915

+1 for the SQL fiddle example, very useful.

I believe this should fix your problem (added a sub select to your join)

SELECT Concat('Category-',c.category_id), 
count(DISTINCT p2c.product_id) as products 
FROM category c 
LEFT JOIN product_to_category p2c 
  ON (c.category_id = p2c.category_id AND p2c.product_id) AND p2c.product_id in
  (select product_id from product_to_category where category_id = @input)
LEFT JOIN category_path cp ON (cp.category_id = c.category_id AND cp.path_id = c.category_id)    
WHERE
cp.level <= 2 
GROUP BY c.category_id
ORDER BY c.sort_order

Upvotes: 1

Related Questions