Recursive get all childs from parent

Question

I've got MLM structure with multiple childs in X-level (not binary tree marketing) So table consist: parentid and managerid (child). It looks like on this picture:

As you can see, Manager 1 is the main manager with parentid=0 Every manager can have child from 0 to XXX For Manager 1, Manager X is in his 4th level (levels are not limited)

So the question is: "How can I coun all managerid for exact parent id"? i.e. for manager 2 (who has 3 levels and 6 managers in it) or manager 6 (who has 2 leves and 5 managers in it).

Database: MySql

CREATE TABLE `accounts` (
  `id` int(11) NOT NULL,
  `managerid` varchar(32) COLLATE utf8_unicode_ci DEFAULT '',
  `parentid` varchar(32) COLLATE utf8_unicode_ci DEFAULT '',
  `name` varchar(256) COLLATE utf8_unicode_ci DEFAULT '',
  `regdate` date DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

EDIT: So I'm looking for query that will return all managerid for an parentid (without using mysql stored procedures). i.e. simple INSERT

id,managerid,parentid,name,regdate
1,1000,0,name1,2018-12-10
2,1001,1000,name2,2018-12-10
3,1002,1001,name3,2018-12-10
4,1003,1002,name4,2018-12-10
5,1004,1003,name5,2018-12-10
6,1005,1004,name6,2018-12-10
7,1006,1005,name7,2018-12-10
8,1007,1006,name8,2018-12-10
9,1008,1007,name9,2018-12-10
10,1009,1008,name10,2018-12-10
11,1010,1009,name11,2018-12-10
12,1011,1010,name12,2018-12-10
13,1012,1011,name13,2018-12-10
14,1013,1012,name14,2018-12-10
15,1014,1013,name15,2018-12-10
16,1015,1014,name16,2018-12-10
17,1016,1015,name17,2018-12-10
18,1017,1016,name18,2018-12-10
19,1018,1017,name19,2018-12-10

So in this example manager=1000 has 18 childs manager 1005 has 13 childs

I need to enter parentid=1005 (for manager 1005) count all his childs and get number = 13 Don't forget that parentid=1005 may have childs on different levels (from 1 to xxx infinity). I need to count all his childs on all his levels (see attached image above, for "manager 2")

Clear;y MySQL, SQL Server does not use backticks (````) for quote characters. — Thom A, Dec 10 '18 at 09:25
Can you please [edit] your post and add relevant example data and the results you expect for them as text? — Corion, Dec 10 '18 at 09:46
The column `parentid` is a foreign key to the same table refering to the column `id` (which I suppose is the primary key)? If so, why is the column a varchar and not an int? — Robert Kock, Dec 10 '18 at 10:41
@RobertKock it's "by design" by Bolt CMS ;) parentid is not referring to column id, it's my mistake, I should change parentid in example ;) — Albert Sayapov, Dec 10 '18 at 10:51
I'm not a MySQL expert, but as far as I know a stored function cannot return a table. Would be very easy otherwise. Give me some time and I'll try to figure out how to do this. — Robert Kock, Dec 10 '18 at 15:01
Have a look at this: https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — Robert Kock, Dec 10 '18 at 15:21
@RobertKock thx Robert. As I can see (and already tried) this one http://sqlfiddle.com/#!9/5de2a/46 works, but right now I need to count all ids, or in another case - show all managerids in 1 column. There was another query http://sqlfiddle.com/#!9/d74210/1 but it didn't worked as I expected ;(( — Albert Sayapov, Dec 10 '18 at 18:19
as I said earlier, this one fiddle: http://www.sqlfiddle.com/#!9/d74210/1 not working for me because it's run only 1 recursion. So it will return only childs from level 1 (where my managerid = parentid), and level 2. But I've got a bunch of levels (right now some thing around 10). Who knows how can I run recursion for X times? — Albert Sayapov, Dec 12 '18 at 07:22

score 0 · Accepted Answer · answered Dec 14 '18 at 11:35

There are 2 ways of solving my problem. First one: use WITH RECURSIVE (available from MySQL 8 version), or you stored procedure. You can find code examples and all other stuff here: https://guilhembichot.blogspot.com/2013/11/with-recursive-and-mysql.html

Recursive get all childs from parent

1 Answers1