MySQL - Adjacency List Model - Get Depth -

i have organization table has id, parent_id, , name column. there 50k rows in table. there 1 top level parent , rest under that. in oracle, able retrieve current depth of particular organization level pseudocolumn:

select id, parent_id, level, name organizations start parent_id = 1 connect prior id = parent_id 

i @ loss of proper way above in mysql is. need fetch entire tree along node's depth in 1 query.

there plethora of questions on stackoverflow have this, none of them seem have answer it, links blogs dubious solutions. surely doable in sort of straight-forward manner?

unfortunately modifying table in way not option, nested sets not possibility.

this totally hilarious. picked +50 bounty on similar question literally yesterday : using mysql query traverse rows make recursive tree

i referenced how stored procedures in dba stackexchange (october 24, 2011)

i post same stored procedures along examples dba stackexchange answer:

code parent given node

delimiter $$ drop function if exists `junk`.`getparentidbyid` $$ create function `junk`.`getparentidbyid` (givenid int) returns int deterministic begin     declare rv int;      select ifnull(parent_id,-1) rv     (select parent_id pctable id = givenid) a;     return rv; end $$ delimiter ; 

code ancenstry given node

delimiter $$ drop function if exists `junk`.`getancestry` $$ create function `junk`.`getancestry` (givenid int) returns varchar(1024) deterministic begin     declare rv varchar(1024);     declare cm char(1);     declare ch int;      set rv = '';     set cm = '';     set ch = givenid;     while ch > 0         select ifnull(parent_id,-1) ch         (select parent_id pctable id = ch) a;         if ch > 0             set rv = concat(rv,cm,ch);             set cm = ',';         end if;     end while;     return rv; end $$ delimiter ; 

code family tree (or descendants) given node

delimiter $$  drop function if exists `junk`.`getfamilytree` $$ create function `junk`.`getfamilytree` (givenid int) returns varchar(1024) charset latin1 deterministic begin      declare rv,q,queue,queue_children varchar(1024);     declare queue_length,front_id,pos int;      set rv = '';     set queue = givenid;     set queue_length = 1;      while queue_length > 0         set front_id = format(queue,0);         if queue_length = 1             set queue = '';         else             set pos = locate(',',queue) + 1;             set q = substr(queue,pos);             set queue = q;         end if;         set queue_length = queue_length - 1;          select ifnull(qc,'') queue_children         (select group_concat(id) qc         pctable parent_id = front_id) a;          if length(queue_children) = 0             if length(queue) = 0                 set queue_length = 0;             end if;         else             if length(rv) = 0                 set rv = queue_children;             else                 set rv = concat(rv,',',queue_children);             end if;             if length(queue) = 0                 set queue = queue_children;             else                 set queue = concat(queue,',',queue_children);             end if;             set queue_length = length(queue) - length(replace(queue,',','')) + 1;         end if;     end while;      return rv;  end $$ 

to demonstrate execution of everything, here sample data

use junk drop table if exists pctable; create table pctable (     id int not null auto_increment,     parent_id int,     primary key (id) ) engine=myisam; insert pctable (parent_id) values (0); insert pctable (parent_id) select parent_id+1 pctable; insert pctable (parent_id) select parent_id+2 pctable; insert pctable (parent_id) select parent_id+3 pctable; insert pctable (parent_id) select parent_id+4 pctable; insert pctable (parent_id) select parent_id+5 pctable; select * pctable; 

here query see parents, ancestries, , family trees

select     id,parent_id,     getparentidbyid(id),     getancestry(id),     getfamilytree(id) pctable; 

give try !!!