List:General Discussion« Previous MessageNext Message »
From:João Cândido de Souza Neto Date:September 27 2006 1:47pm
Subject:Re: Count of children
View as plain text  
You can use it:

SELECT parent_id, count( * )
FROM table
WHERE parent_id =1
GROUP BY parent_id

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,

Count of childrenAndré Hänsel27 Sep
  • Re: Count of childrenjoao27 Sep
  • re: Count of childrenRob Desbois27 Sep
    • AW: Count of childrenAndré Hänsel27 Sep
    • Re: Count of childrenjoao27 Sep
      • Re: Count of childrenDouglas Sims27 Sep
  • Re: Count of childrenPeter Brawley27 Sep
    • AW: Count of childrenAndré Hänsel27 Sep
      • Re: AW: Count of childrenddevaudreuil27 Sep
      • Re: AW: Count of childrenPeter Brawley27 Sep
        • RE: AW: Count of childrenRobert DiFalco27 Sep