count number of hierarchical childrens in sql

Question

I have a table that stores parent and left child and right child information. How do i count number of children belongs that parent?

for example my table structure is:

parent  left  right
--------------------
 1        2     3
 3        4     5
 4        8     9
 5        10    11
 2        6     7
 9        12    null

How do I count number of sub nodes for any parent.
For example 4 contains following hierarchical child nodes - 8,9,12 so number of children are 3.
3 contains following sub nodes -> 4,5,10,11,8,9,12 so total number of children 7.

How do I achieve this using SQL query?

possible duplicate of [Count all child nodes of hierarchical data in a table](http://stackoverflow.com/questions/6061025/count-all-child-nodes-of-hierarchical-data-in-a-table) — AdamMc331, May 29 '15 at 15:56
If this is a "nested set", it is anti-relational. Please remove the relational-database tag. — PerformanceDBA, May 30 '15 at 09:41

Drew · Answer 1 · 2015-05-29T20:59:23.850

create table mytable
(   parent int not null,
    cleft int null,
    cright int null
)
insert into mytable (parent,cleft,cright) values (1,2,3);
insert into mytable (parent,cleft,cright) values (2,6,7);
insert into mytable (parent,cleft,cright) values (3,4,5);
insert into mytable (parent,cleft,cright) values (4,8,9);
insert into mytable (parent,cleft,cright) values (5,10,11);
insert into mytable (parent,cleft,cright) values (6,null,null);
insert into mytable (parent,cleft,cright) values (7,null,null);
insert into mytable (parent,cleft,cright) values (8,13,null);
insert into mytable (parent,cleft,cright) values (9,12,null);
insert into mytable (parent,cleft,cright) values (10,null,null);
insert into mytable (parent,cleft,cright) values (12,null,null);
insert into mytable (parent,cleft,cright) values (13,null,17);
insert into mytable (parent,cleft,cright) values (17,null,null);


DELIMITER $$
CREATE procedure GetChildCount (IN parentID INT)
DETERMINISTIC
BEGIN
    declare ch int;
    declare this_left int;
    declare this_right int;
    declare bContinue boolean;
    declare count_needs_scan int;

    create temporary table asdf999 (node_id int,processed int);
    -- insert into asdf999 (node_id,processed) values (1,0);
    -- update asdf999 set processed=1;

    SET ch = parentID;
    set bContinue=true;
    while bContinue DO
        -- at this point you are sitting at a ch (anywhere in hierarchy)
        -- as you are looping and getting/using children

        -- save non-null children references: -----------------------------
        select cleft into this_left from mytable where parent=ch;
        if !isnull(this_left) then
            insert asdf999 (node_id,processed) select this_left,0;
        end if;

        select cright into this_right from mytable where parent=ch;
        if !isnull(this_right) then
            insert asdf999 (node_id,processed) select this_right,0;
        end if;
        -- -----------------------------------------------------------------
        select count(*) into count_needs_scan from asdf999 where processed=0;
        if count_needs_scan=0 then
            set bContinue=false;
        else
            select node_id into ch from asdf999 where processed=0 limit 1;
            update asdf999 set processed=1 where node_id=ch;
            -- well, it is about to be processed
        end if;
    END WHILE;
    select count(*) as the_count from asdf999;
    drop table asdf999;
END $$
DELIMITER ;

call GetChildCount(2);  -- answer is 2
call GetChildCount(4);  -- answer is 5

I could supply a version that creates a dynamically named table (or temp table) and clobbers it at end if you want . "dynamic sql / prepare statment" inside of a procedure. that way users won't step on each other with shared use of the work table asdf999. so this is not production ready. but the above gives you an idea of the concept

count number of hierarchical childrens in sql

1 Answers1