Treestructure in SQL

[Thomas Weholt]

Hi,

I need to define tree-like structure for content categories in a simple CMS
project. I got a table in a SQLite database which looks like this :

    CREATE TABLE category (
      CAT_ID INTEGER PRIMARY KEY,
      CAT_PARENT_ID integer,
      CAT_NAME varchar(100) NOT NULL,
      CAT_DESCRIPTION varchar(250),
    );

It makes is possible to create a tree of categories, linking subnodes in the
the tree to parent nodes using the CAT_PARENT_ID and it works nice, but I'm
having trouble visualizing it.

Say I got these records in the table :
Id, Parent-id, Name
1, 0, Games
2, 1, Counter-strike
3, 1, Boardgames
4, 0, Programming
5, 4, Python
6, 4, XML
7, 5, Web

Where 0 as parent-id symbolizes "root"-level of the treestructure.

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??

Best regards,
Thomas