Treestructure in SQL

[Diez B. Roggisch]

> 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
> 
> Any clues or hints??

Its not possible in a generic way. what would be possible is to create one
for a certain number of levels:

select lv0.Name, lv1.Name from category lv0, category lv1 where lv0.cat_id =
lv1.parent_id

You can extend this to every level you want.

The reason is that for every parent-child relation, the database needs a
cross-product between the two sets the relation is build upon. On the
cross-product (which will contain _all_ possible relation, e.g
(Games,Python), the filtering criteria is applied.

I'm currently not sure what happens if you look for three levels, but look
at a two-level branch. Maybe outer joins help you there, but I'd have to
play around with that - and for that I'd have to setup a database right
here :)

Regards,

Diez