2

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

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
JoeT
  • 21
  • 1
  • 2
  • 1
    Have you try anything? – oNare Aug 05 '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 05 '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 05 '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 - Slava Ukraini 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

2 Answers2

2

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

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

PictureFromOtherPost

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 !!!

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
0

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.

Rick James
  • 78,038
  • 5
  • 47
  • 113