Re: select all fields plus total children

[mos]

At 01:28 PM 3/17/2005, you wrote:
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
Brian,
You need to do a Left Join as in:
SELECT c.*, sum(1-ISNULL(p.id)) AS total
FROM category AS c left join product AS p on c.id=p.category_id
WHERE c.parent_id = 0
GROUP BY c.name
The left join will join the tables and if there is no corresponding row on 
the right, it fills it with null.
The sum(1-ISNULL(p.id)) will sum all occurrences of p.id that are not null 
as 1, and sums 0 if they are null. You need to do that because p.id is NULL 
when there is no corresponding product row.

Ok, slap away! 
Mike  

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: select all fields plus total children

[SGreen]

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