Selecting hierarchial data

Question

Post reply

Selecting hierarchial data

tomiz

SSCommitted

Points: 1842
More actions
November 21, 2003 at 8:37 am

#83565
Hello gurus!
I'm trying to arrange some articles (links to articles that is) into a category structure. Now, I think I know the basics of storing hierarchial data in a table. It should be something like this:
myTable
categoryID
categoryName
parentID
Now, my question is, how do I select the categories?
I know I can get the main categories like this:
```
SELECT * FROM myTable WHERE parentID = NULL
```
or
```
SELECT * FROM myTable WHERE parentID = categoryID
```
And then I can get the subcategories or a specific category like so:
```
SELECT * FROM myTable WHERE parentID = @parentID
```
But how do I get all the categories nicely arranged in a single query?
I would like the output to be something like this:
- biology
-- prehistoric
--- dinosaurs
--- mammals
-- monkeys
--- baboons
....
Well, you get the picture. Is there a simple, easy and fast solution to do this?
/Tomi
PS. This is my 100th post! Whoooopie!
Edited by - tomiz on 11/21/2003 08:40:23 AM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

cmore SSCrazy Points: 2452 More actions · Answer 1

Not unless you can accept a depth limit.

For infinite-depth heirarchies I believe you are stuck with doing it yourself in a loop.

Look in online help for 'expanding hierarchies'

tomiz SSCommitted Points: 1842 More actions · Answer 2

Well, that's exactly what I'm looking for! I guess I can have a limit...

Thanks a lot!

/Tomi

cmore SSCrazy Points: 2452 More actions · Answer 3

I meant that the other way- if you custom code a loop to build a heirarchy from a rowset of child/sponsor records, you don't have to worry about a depth limit like you do when you use joins (number of involved tables limit) or UDFs (nesting limit).

tomiz SSCommitted Points: 1842 More actions · Answer 4

tomiz

SSCommitted

Points: 1842

November 21, 2003 at 12:20 pm

#483205

Oh, I see... Thanks again!

/Tomi

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

quote:
But how do I get all the categories nicely arranged in a single query?
I would like the output to be something like this:
- biology
-- prehistoric
--- dinosaurs
--- mammals
-- monkeys
--- baboons
....
Well, you get the picture. Is there a simple, easy and fast solution to do this?
/Tomi
PS. This is my 100th post! Whoooopie!