Re: find direct childs in a tree structure

[Chris Stubben]

Hi,

I'm not sure what you mean by a direct node and indirect node, so I may need some more information.

This will get all the children below id=6, right?

SELECT s.*
FROM kategorien AS s, kategorien AS v
WHERE (s.links BETWEEN v.links AND v.rechts)
AND v.id = 6;


I guess you could find the level of each child below id=6 using this query


SELECT s.id, count(v.id) as level
FROM kategorien AS s, kategorien AS v, kategorien as sub
WHERE (s.links BETWEEN v.links AND v.rechts)
AND sub.id = 6
AND (s.links BETWEEN sub.links AND sub.rechts)
AND (v.links BETWEEN sub.links AND sub.rechts)
GROUP BY s.id;


and then add a having clause to get 'direct' child records (immediate subordinates?)

HAVING count(v.id)=2;

Or just include a parent id link in your table - its faster for searching up a tree or finding immediate subordinates.


Chris