Category parent subcategory level

Category parent subcategory level

SELECT t1.id, t1.parent_id, t1.title,
( if(t4.id is null, 0,1) + if(t3.id is null, 0,1) + if(t2.id is null, 0,1) + if(t1.id is null, 0,1)) as level,
trim(leading '; ' from concat(ifnull(t4.title, ''), '; ', ifnull(t3.title, ''), '; ', ifnull(t2.title, ''), '; ', ifnull(t1.title, ''))) as path
FROM team t1
LEFT JOIN team t2 ON t1.parent_id=t2.id
LEFT JOIN team t3 ON t2.parent_id=t3.id
LEFT JOIN team t4 ON t3.parent_id=t4.id;