Counting how many childs a parent category has

[worchyld]

A drop-down box has a list of all the "product categories" and it also shows all the children each "product category" has. Unfortuently the d/d box is way too long and is very confusing.

What I'm trying to do is cut down the number of items in the d/down list and repetitive SQL queries. In order to do this I need to do a SQL query to count how many childs a parent category has.

The table structure is thus;

# Pseduocode
table product_category {
        var id (primary key)
        var parent_id (secondary key)
        var name (the name of the category)
} #@end:table

So, this would mean having to do a recursive SQL query to go through this table to find out how many childs a parent category has.

Currently we use a php function and it recursively loads the function and spits out all the data, some of which is totally unnecessary.. All I need is the ultimate child of a parent (or where there is no more children) and return the COUNT variable.

Is there a way to do this? I'm thinking it would be an inner join on itself? Is this right?

I hope you can help.

Thanks.

[worchyld]

Here's what I have so far;

SELECT p.id, p.name, p.parent_id, count( c.id ) AS childcount
FROM product_category AS p
LEFT OUTER JOIN product_category AS c ON p.id = c.parent_id
WHERE p.parent_id !=0
GROUP BY p.id, p.name, p.parent_id
ORDER BY `p`.`id` ASC

However I can't seem to add the phrase "AND (count(c.id) = 0)" after the "WHERE p.parent_id !=0" bit...

Is there a way to find only those product categories where there are no children?

Thanks.

[longneck]

with the current way tou have your parent/child relationship set up, you can't do this in a single query.

[r937]

are you looking to count only the childless children only, all children only, all children and grandchildren (descendants), or only childless descendants?

i guess the key question is, if you want to go down the tree more than one level, then you can't do it unless you also put a limit on the number of levels going down (each of which will require an additional LEFT OUTER self-join)

[worchyld]

I've been able to resolve the problem using the query I posted before and reading your comments. Many thanks r937 and longneck.