how to query a table based on parentid

[billdvldog]

I have a table with:

dataid int
parentid int
dirname varchar(248)

Basically it is a list of directories with associated parent directories. I have been asked to write a query that lists the top directory first, then each of the top directory's direct child directories with each of the child directory's direct child directories under them and so on. Below is sample table information and what the result should look like. The number of child directories may increase. We currently have about 5 levels.

I would appreciate any help/direction you gurus can give me! I am at a complete loss.

Thanks,

Bill

-- Sample Table Data

dataid, parentid,dirname

4000, 1000, Contract
5436, 4000, Task1
5695, 4000, Task2
5621, 4000, Task3
5840, 5436, MIS Weekly Report
5928, 5436, CMS Progress Report
6403, 5695, MISS Monthly Report

-- The result set should look like this:

Contract
Task1
MIS Weekly Report
CMS Progress Report
Task2
MISS Monthly Report
Task3

 

[SQLSister]

How do I do ___________ with a Hierarchical structure? faq183-5322

Questions about posting. See faq183-874