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


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

  • 1
    Have you try anything? – oNare Aug 5 '15 at 11:34
  • truth is i haven't tried anything.. im just finding and asking solutions in web, im just new in php... sorry :( – JoeT Aug 5 '15 at 11:35
  • 1
    Well, the best you can do is edit your question and add your CREATE TABLE statements and some test data from each one, and, welcome to dba.SE. – oNare Aug 5 '15 at 11:41
  • 1
    A recursive CTE can be thought of as a variable number of joins. In your case there is an upper limit on the number of joins, so you can just do a left self join 6 times to build the tree. – Lennart Feb 22 '16 at 18:35
  • You can use lft and rgt as two fields to manage whole tree structure fast and traversal and easy way : please check Link OR Link form more information. – bharat Dec 12 '16 at 19:35

I have bad news and good news


MySQL does not support recursive CTE queries

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


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

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`) )

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.


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


  • 1
    If you want the code written for you, I am sure @RickJames or I can do so (we charge by the hour ;-) ) – RolandoMySQLDBA Aug 6 '15 at 12:14

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.