# COUNTING results from self-referenced table recursively

I have a table called Clients. And at this table, I have an `idFather` to know who added this row to the table.

Sample:

``````id | user    | idFather | createdAt
---+---------+----------+-----------
01 | raphael | null     | 01-06-2016
02 | jose    | 01       | 03-06-2016
03 | ryan    | 01       | 02-06-2016
04 | alyssa  | 03       | 04-06-2016
05 | laysa   | 04       | 04-06-2016
06 | karen   | 02       | 04-06-2016
07 | roger   | 03       | 04-06-2016
``````

Now i need know how much peoples an user invited indirectly. Sample:

``````The user (01 - Raphael ) invited the users ( 02 and 03 ) so:
Raphael invited 2 directly
And user (02 - Jose) invited no one
But user (03 - Ryan) invited (04 and 07) total 2
And user (04 was invited by 03) invited also the user (05) total 1

So Raphael invited 2 directly ( this i did not know )
And Raphael invited 3 indirectly ( this i need know )
``````

I i'm trying look 7 levels under the user... i'll try explain with this tree:

EDIT: If the users from lv1 was hard to kick out from Couting... I can do this math on my system... i just need do recursively by 7 lvs... and count just lv1 and get the difference.

I'm looking to get a query result like this:

``````user    | indirectInviteds
--------+-----------------
raphael | 4
``````

I'll search just 1 user per time... i`ll not try select ALL clients and look directly.. it will explode...!!!

Thanks for the help guys!

As your data is a tree structure, consider using the Nested Set data model.

A good write-up can be found at Mike Hillyer's website, here: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

complete with lots of SQL to manage the tree data.

• Can you give an sample? I did not understand how exactly to do it... Jul 12, 2016 at 2:59
• I recently used Mike Hillyer's article to store tree data in a database using the nested set model. I wouldn't be able to add much that isn't in the article. It may be worth creating a table with the same columns and data that he uses and work through his SQL examples. That's what I did. I also drew a tree on a piece of paper and went through how to number the left and right values for each node. FWIW, It took me a little while before I understood it, so put a couple of hours aside to have a play. I think Mike's article is well written and a good introduction to the topic. Regards, Jul 12, 2016 at 7:42

MySQL has no simple way to do it.

Assuming you have no "loop" in the "tree", ... Write a stored routine to iterate until there are no more "children". Or, do the equivalent in client code, not SQL.

• Can you give an sample code to iterate? it will not have loop inside.... Jul 12, 2016 at 2:59