How can I count all children & grandchildren until the 7th level?

Question

I have 3 tables right now

 1. members
 2. members_parents
 3. members_status

How can i count the children & grandchildren until to the depth level and separate the count() for the status 0 = free and 1 = subscriber

table: members - list all the records of members
table: members_parents - list of members & the parent member
table: members_status - list the members & parents status where 0 or 1

tbl_members_parents:

id   member     parent
1    10000      NULL
2    10001      10000
3    10002      10001

and so on ...

How can i count the child & grandchildren until to the 7th level of grand children ?

Thank You

Answer 1

I have bad news and good news

BAD NEWS

MySQL does not support recursive CTE queries

Like @RickJames, I don't have the energy to write it for you

GOOD NEWS

Once upon a time, I did have the energy and time to write stuff like this

Here are my posts I have written discussing Stored Procedures and Recursive Queries

• Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs
• Dec 10, 2012 : MySQL: Tree-Hierarchical query
• Jul 11, 2013 : Recursive self joins
• Jan 31, 2014 : Recursive Query in MySQL using stored proceedure and CURSOR
• Nov 28, 2014 : Implement a Parent-Level Count in Binary Search Tree

From the first link, I addressed this table structure

CREATE  TABLE IF NOT EXISTS `testdb`.`observations` (
  `id` INT NOT NULL ,
  `parent_id` INT NULL ,
  `name` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

that was holding this data

I made three Stored Procedures centered around them

• GetParentIDByID
• GetAncestry
• GetFamilyTree

What's interesting about the code is that I do not use recursion. I use a String as a comma-separated list of node IDs and process it like a queue. This technique is known as PreOrder Tree Traversal except each tree node can have more than two children.

SUGGESTION

What you need to do is read the code from that first post and apply it with your fields and keys in mind.

GIVE IT A TRY !!!

Answer 2

It will required a recursive Stored Procedure. Sorry, I don't have the energy to write it for you.

See http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_sp_recursion_depth ; perhaps you can use max_sp_recursion_depth=7 to automatically stop at that depth. In any case, you need to set it, since recursion is disabled by default.