mysql - count number of hierarchical childrens in sql -

i have table stores parent , left child , right child information. how count number of children belongs parent?

example 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 count number of sub nodes parent.
for example 4 contains following hierarchical child nodes - 8,9,12 number of children 3.
3 contains following sub nodes -> 4,5,10,11,8,9,12 total number of children 7.

how achieve using sql query?

create table mytable (   parent int not null,     cleft int null,     cright int null ) insert mytable (parent,cleft,cright) values (1,2,3); insert mytable (parent,cleft,cright) values (2,6,7); insert mytable (parent,cleft,cright) values (3,4,5); insert mytable (parent,cleft,cright) values (4,8,9); insert mytable (parent,cleft,cright) values (5,10,11); insert mytable (parent,cleft,cright) values (6,null,null); insert mytable (parent,cleft,cright) values (7,null,null); insert mytable (parent,cleft,cright) values (8,13,null); insert mytable (parent,cleft,cright) values (9,12,null); insert mytable (parent,cleft,cright) values (10,null,null); insert mytable (parent,cleft,cright) values (12,null,null); insert mytable (parent,cleft,cright) values (13,null,17); insert 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 asdf999 (node_id,processed) values (1,0);     -- update asdf999 set processed=1;      set ch = parentid;     set bcontinue=true;     while bcontinue         -- @ point sitting @ ch (anywhere in hierarchy)         -- looping , getting/using children          -- save non-null children references: -----------------------------         select cleft this_left mytable parent=ch;         if !isnull(this_left)             insert asdf999 (node_id,processed) select this_left,0;         end if;          select cright this_right mytable parent=ch;         if !isnull(this_right)             insert asdf999 (node_id,processed) select this_right,0;         end if;         -- -----------------------------------------------------------------         select count(*) count_needs_scan asdf999 processed=0;         if count_needs_scan=0             set bcontinue=false;         else             select node_id ch asdf999 processed=0 limit 1;             update asdf999 set processed=1 node_id=ch;             -- well, processed         end if;     end while;     select count(*) the_count asdf999;     drop table asdf999; end $$ delimiter ; 

---

call getchildcount(2);  -- answer 2 call getchildcount(4);  -- answer 5 

i supply version creates dynamically named table (or temp table) , clobbers @ end if want . "dynamic sql / prepare statment" inside of procedure. way users won't step on each other shared use of work table asdf999. not production ready. above gives idea of concept