I've found this website to be extremely helpful:
André Hänsel <haensel@stripped>
09/27/2006 10:55 AM
AW: Count of children
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@stripped]
> Gesendet: Mittwoch, 27. September 2006 16:49
> An: André Hänsel
> Cc: mysql@stripped
> Betreff: Re: Count of children
> >I want the count of all sub-entries for a specific entry.
> Depends on the model you are using--edge list or nested sets?
> 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
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
CONFIDENTIALITY NOTICE:This email is intended solely for the person or
entity to which it is addressed and may contain confidential and/or
protected health information. Any duplication, dissemination, action
taken in reliance upon, or other use of this information by persons or
entities other than the intended recipient is prohibited and may violate
applicable laws. If this email has been received in error, please notify
the sender and delete the information from your system. The views
expressed in this email are those of the sender and may not necessarily
represent the views of IntelliCare.