Join, group by, for listing categories with products quantities

[hmoner]

Hi guys
I'm not having a nice time trying to make this query work
I have 2 tables
category
product

I want to list the following way
CATEGORY.NAME | CATEGORY.DESC | PRODUCT_COUNT

The following query works, but it doesn't list the categories that doesn't have products added. I'd like (if possible) to show "0" in PRODUCT_COUNT column, when the category doesn't have products added.

; ==========
SELECT category.id, COUNT(product.fk_category) AS product_count, category.name, category.description FROM product, category WHERE category.id = product.fk_category
GROUP BY product.fk_category
ORDER BY category.name ASC
; ==========

Thanks a lot in advance...

 

[r937]

what you need is a LEFT OUTER JOIN

change your query to --

SELECT category.id, COUNT(product.fk_category) AS product_count, category.name, category.description
[red]FROM category LEFT OUTER JOIN product
ON[/red] category.id = product.fk_category
GROUP BY [red]category.id[/red]
ORDER BY category.name ASC


you're using MySQL, right? ;-)



r937.com | rudy.ca

 

[hmoner]

Hi r937
Yes, I'm using mySQL

I've tried the following and worked:

SELECT category.id, COUNT(product.fk_category) AS product_count, category.name, category.description FROM product RIGHT JOIN category ON category.id = product.fk_category
GROUP BY category.id
ORDER BY category.name ASC

Which one should I use? LEFT OUTER JOIN or RIGHT JOIN ? and why ?

Thanks a lot again

 

[r937]

you should use whichever makes sense for you

most people think from left to right

perhaps you more comfortable to left from right thinking are



note that

category LEFT OUTER JOIN product

is exactly the same as

product RIGHT OUTER JOIN category



r937.com | rudy.ca

 

[PHV]

Anyway the GROUP BY clause is incomplete in ANSI SQL:

SELECT category.id, COUNT(product.fk_category) AS product_count, category.name, category.description
FROM product RIGHT JOIN category ON category.id = product.fk_category
GROUP BY category.id[!], category.name, category.description[/!]
ORDER BY category.name ASC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

indeed, phv, which was the clue that it was mysql

have you seen Debunking GROUP BY myths?

i'm actually rather fond of the incomplete--on-purpose--GROUP-BY




r937.com | rudy.ca