Recursion & MYSQL ?

Question

I got a really simple table structure like this:

Table Page Hits

id | title  | parent | hits
---------------------------
 1 | Root   |        | 23
 2 | Child  |      1 | 20
 3 | ChildX |      1 | 30
 4 | GChild |      2 | 40

As I don't want to have the recursion in my code I would like to do a recurisive SQL.

Is there any SELECT statement to get the sum of Root (23+20+30+40) or Child ( 20 + 40 ) ?

Answer 1

Not in 1 select statment, no.

If you knew the maximum depth of the relationshop (ie parent->child->child or parent->child->child->child) you could write a select statement which would give you a bunch of numbers that you would then have to sum up seperately (1 number per level of depth).

You could, however, do it with a mysql stored procedure which is recursive.

Answer 2

You are organizing your hierarchical data using the adjacency list model. The fact that such recursive operations are difficult is in fact one major drawback of this model.

Some DBMSes, such as SQL Server 2005, Postgres 8.4 and Oracle 11g, support recursive queries using common table expressions with the WITH keyword.

As for MySQL, you may be interested in checking out the following article which describes an alternative model (the nested set model), which makes recursive operations easier (possible):

• Mike Hillyer: Managing Hierarchical Data in MySQL