1

Is it possible, using only MySQL, to write a query/function to get the following results?

Example Table: 

+------------+
| ID | Child |
+------------+
|  1 | 2     |
|  1 | 3     |
|  2 | NULL  |
|  3 | 4     |
|  4 | 5     |
|  4 | 6     |
|  5 | NULL  |
|  6 | NULL  |
|  7 | NULL  |
+------------+

Example Results: 

+------------+
| ID | Child |
+------------+
|  1 | 5     |
|  2 | 0     |
|  3 | 3     |
|  4 | 2     |
|  5 | 0     |
|  6 | 0     |
|  7 | 0     |
+------------+

In plain English: The COUNT of each ID and all of their children... and their children.

1's children are: 2, 3, 4, 5, 6
3's children are: 4, 5, 6
4's children are: 5, 6

I imagine it has to do with some clever use of subqueries/functions/grouping, then SUM(COUNT(CHILD)).

Caleb Gray
  • 2,980
  • 2
  • 20
  • 31
  • 1
    Ref: http://stackoverflow.com/questions/2191847/sql-cte-counting-childs-recursion – Ara Jan 31 '12 at 22:40
  • 1
    Using "with" with MySQL related to Ara's linked answer: http://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql – Highway of Life Feb 01 '12 at 15:56

1 Answers1

1

You're not going to like this, but take a look at this article (yes it is long, sorry :P)

Mosty Mostacho
  • 41,294
  • 14
  • 93
  • 121
  • Unfortunately they're using a "trick" by using the lft and rgt columns. I'm hoping to solve this with just two columns. – Caleb Gray Feb 01 '12 at 16:09
  • 1
    Hmm... AFAIK that is just not possible. You can solve it that way IF you have a fixed (and small) amount of children in which you can create subqueries for all of them. But when it comes to many levels of depth, then I don't think you'll be able to solve it because MySQL doesn't support recursive queries. Besides, there isn't much overhead adding just 1 column :) – Mosty Mostacho Feb 02 '12 at 01:44
  • Your response is as accurate as it gets, as far as I can tell. That is, unless it's possible to create a recursive function/procedure in the newer MySQL API...? – Caleb Gray Mar 01 '12 at 22:43