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
Non-Fiction

CDs Pop
Classic

Phones

.....
....

How to write SQL query to get result in this format. Here each child item can have more child items.
K
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:
http://www.sitepoint.com/article/hierarchical-data-database
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:

http://www.amazon.com/Hierarchies-Smarties-Kaufmann-Management-Systems/dp/1558609202/

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

http://www.mvps.org/access/downloads/bom.zip
trollop
Tuesday, January 01, 2008
 
 
Oops. adampt <<<< adapt
trollop
Tuesday, January 01, 2008
 
 

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

Other recent topics Other recent topics
 
Powered by FogBugz