List:General Discussion« Previous MessageNext Message »
From:brian ally Date:March 17 2005 7:28pm
Subject:select all fields plus total children
View as plain text  
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
Thread
select all fields plus total childrenbrian ally17 Mar
  • Re: select all fields plus total childrenSGreen17 Mar
  • Re: select all fields plus total childrenmos17 Mar