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: 2402 More actions · Answer 1

cmore

SSCrazy

Points: 2402

November 21, 2003 at 8:56 am

#483135

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

tomiz

SSCommitted

Points: 1842

November 21, 2003 at 11:53 am

#483202

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

Thanks a lot!

/Tomi

cmore SSCrazy Points: 2402 More actions · Answer 3

cmore

SSCrazy

Points: 2402

November 21, 2003 at 11:56 am

#483203

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!