André

With an edge list, the solution entails recursion, so you need either an sproc or application proc. With a nested sets model, the count is dead simple. If the id of the target row is N, and the left & right node columns are named leftedge and rightedge, the query is

SELECT COUNT(t2.id)
FROM tbl t1
JOIN tbl t2 ON t2.leftedge > t1.leftedge AND t2.leftedge < t1.rightedge
WHERE t1.id=N;

PB

-----

André Hänsel wrote:

I will use any model that is suitable. ;)

I am somewhat familiar with both tree models but I can't come up with a
method to get the count of all sub- and sub-sub-nodes in either of them.

-----Ursprüngliche Nachricht-----
Von: Peter Brawley [mailto:peter.brawley@earthlink.net] 
Gesendet: Mittwoch, 27. September 2006 16:49
An: André Hänsel
Cc: mysql@lists.mysql.com
Betreff: Re: Count of children

André,

I want the count of all sub-entries for a specific entry.

Depends on the model you are using--edge list or nested sets?

PB

-----

André Hänsel wrote:

I have a table with id and parent_id.
I want the count of all sub-entries for a specific entry.

I found several documents about working with graphs/trees

in MySQL but I

could not find a solution for my problem.

I can imagine two possibilities, but one is memory

intensive and the other

one creates load on updates.
The first is, that I select all entries and then use a

procedural language

to determine recursively whether an node is a sub-node of

the specific node.

The second is, that I store the sub-node count with each

node and when I do

an insert, I walk the tree upwards and increment the node-counts.

Is there a smart solution/best practice for my problem?

Now I can't think of another sentence starting with an i. ;-)

Best regards,
André

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.407 / Virus Database: 268.12.9/457 - Release 
Date: 9/26/2006