By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,448 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

SQL query to get tree details

P: n/a
RP
I have an Access 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

......
.....

The problem is that each child item can have even more child items,
hence a new column is needed.
How to write SQL query to get result in this format.
Dec 19 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
SQL is really not very good at this kind of thing.

Here's a starting point for some reading:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

Infinite recursion is part of the problem here, e.g. where an item is its
own grandparent. What I usually do when working with this kind of data is to
cheat, and insist that the data must resolve in a set number of generations
(typically between 3 and 9, depending on the data.) I then resolve it into
an unnormalized temp table using VBA. You can then identify any records that
did not resolve, and insist that the user deals with those before
continuing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"RP" <rp*********@gmail.comwrote in message
news:1c**********************************@s19g2000 prg.googlegroups.com...
>I have an Access 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

.....
....

The problem is that each child item can have even more child items,
hence a new column is needed.
How to write SQL query to get result in this format.
Dec 19 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.