How to query a table and order it based on parent ids

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

How to query a table and order it based on parent ids

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-10-29 : 08:27:08

Bill writes "Windows 2000 Server SP4/SQL Server 2000 SP4

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"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-29 : 09:00:31

take a look at this:
http://www.seventhnight.com/treestructs.asp

Corey

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-29 : 09:23:15

and
http://www.nigelrivett.net/RetrieveTreeHierarchy.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-29 : 09:42:32

Or this (which looks about like Nigel's without the spacing)


set nocount on
declare @level int
set @level = 1

create table #tree (id int identity (1,1), dataid int, level int)

insert #tree (dataid, level)
	select dataid, @level
	from dirtable where parentid = 0 -- or is null

While @@rowcount > 0
begin
	set @level = @level + 1
	insert #tree (dataid, level)
		select d.dataid, @level 
		from dirtable d
		inner join #tree t on t.dataid = d.parentid
		where t.level = @level - 1
		order by dirname
end

Select dirname from #tree t
inner join dirtable d on t.id = t.dataid
order by id

drop table #tree

I'm curious to know why you want this, because it does not seen very useful.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources