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
  • 3,040
  • 2
  • 21
  • 32
  • 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

0 Answers0