The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SQL query to retrieve tree

I have a MS Access 97 Table with following columns:

GID (auto number)
PID number
TreeID Text
ItemName Text

This table consists of records in the following manner:

GID    PID  ItemName
----- ------ ------------
1      1    RootNode
2      1    Books
3      1    CDs
4      1    Phones
5      2    Fiction
6      2    Non-Fiction
7      3    Pop
8      3    Classic

I want to get results in this format:

Category SubCategory
---------- ---------------
Books Fiction

CDs Pop



How to write SQL query to get result in this format. Here each child item can have more child items.
Wednesday, December 19, 2007
You have to use recursion to retrieve a tree, stored like that. If your database supports it, you could implement that with stored procedures.

Alternatively, you could pick another storage scheme, such as modified preorder. Have a look at:
Troels Knak-Nielsen Send private email
Wednesday, December 19, 2007
Some database engines support CONNECT BY PRIOR and START WITH clauses, with those you can implement complete tree queries. I don't know if access supports that though.
Zoltan Mezei Send private email
Wednesday, December 19, 2007
Buy a copy of Joe Celko's SQL book on Trees & recursive database structures:

Warning: It's an advanced topic, but one well worth knowing.
Wednesday, December 19, 2007
Pick apart and adampt this Access VBA Bills of Material mdb. It uses a stack rather than recursing.
Tuesday, January 01, 2008
Oops. adampt <<<< adapt
Tuesday, January 01, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz