brian ally <[EMAIL PROTECTED]> wrote on 03/17/2005 02:28:46 PM:
> I have 2 tables, category & product. product contains category_id which
> points back to category.id
>
> I'm trying to get all fields for each category plus the total no. of
> products in that category. A simple query for this:
>
> SELECT c.*, COUNT(p.id) AS total
> FROM category AS c, product AS p
> WHERE c.parent_id = 0 AND p.category_id = c.id
> GROUP BY c.name
>
> unfortunately will not show any categories which currently have no
> products assigned. I'm sure i need a join in there but haven't found it.
>
> I'm also sure i'll slap my forehead when i see the proer way to do this.
> Any help appreciated.
>
> brian
>
I hate to be the bearer of bad new but you ALREADY have a join in
there.... You created an implicit INNER JOIN when you said:
FROM category AS c, product AS p
WHERE c.parent_id = 0 AND p.category_id = c.id
That phrase is semantically equivalent to:
FROM category AS c
INNER JOIN product AS p
ON c.parent_id = 0 AND p.category_id = c.id
You tell us that you need all category records but only those product
records that exist, that's the purpose of the two "outer" JOIN predicates
(to give you all of one table and any that match from another). The
DIRECTION of the JOIN determines which table is the "all" table and which
table is the "optional". Change your query to use
FROM category AS c
LEFT JOIN product AS p
ON c.parent_id = 0 AND p.category_id = c.id
and you will get the results you wanted because your category table is on
the "left" side of the JOIN.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
