Treestructure in SQL

[Kylotan]

"Thomas Weholt" <2002 at weholt.org> wrote in message news:<KZPwb.1426$Y06.19413 at news4.e.nsc.no>...

> Now I need either a SQL-statement compatible with SQLite or some code
> snippet that will give me :
> 
> Games
> Games, Counter-strike
> Games, Boardgames
> Programming
> Programming, Python
> Programming, Python, Web
> Programming, XML

Is it a big problem to make several SELECTS in a loop?

The first select gets all rows where parent_id = 0. Then print that
node, and then for each row in that SELECT result, search for all
nodes that have parent_id equal to this one, and so on. You probably
want to do it recursively rather than iteratively if you want
depth-first traversal as shown in your example.

Alternatively, if I was always showing the entire list of nodes, I'd
just do one SELECT and create their tree order in code.

-- 
Ben Sizer