sql query number of parent

Question

i have table called type

ID  Name     ParentID
---------------------
1   name1    0
2   name2    0
3   name3    1
4   name4    2
5   name1    1

i need to know how many parent (descendants) each type have EX:

ID -------- descendants

ID-> 1   (have no parent)
ID-> 3   (have 1 parent (ID->1))
ID-> 5   (have two parent ((ID->3(ID->1))))

how to made optimized sql statement do this using mysql?

Answer 1

MySQL unfortunately doesn't support recursive CTE's. But if the number of parents is limited, you can implement this using joins:

select p.id
,      case 
           <... more whens here ...>
           when c3.id is not null then 3
           when c2.id is not null then 2
           when c1.id is not null then 1
           else 0
       end as NumberOfChildren
from yourtable p
left join yourtable c1 on c1.parentid = p.id
left join yourtable c2 on c2.parentid = c1.id
left join yourtable c3 on c3.parentid = c2.id
<... more joins here ...>
group by p.id

Answer 2

Also you could implement function to compute level
Sort of:

create function get_level(_id int) returns int
begin
  declare _level int default -1;
  repeat
    set _level = _level + 1;

    select parent_id
    into _id
    from your_table
    where id = _id; 
  until _id is null
  end repeat;
  return _level;  
end

Usage:

select id, get_level(id)  
from your_table  

I do not test a code. That approach is not effective. In most cases advice to store and compute level on insert/update is better.