SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 How to query a table and order it based on parent ids
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/29/2004 :  08:27:08  Show Profile  Visit AskSQLTeam's Homepage
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 10/29/2004 :  09:00:31  Show Profile  Visit Seventhnight's Homepage
take a look at this:
http://www.seventhnight.com/treestructs.asp


Corey
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 10/29/2004 :  09:23:15  Show Profile  Visit nr's Homepage
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.
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 10/29/2004 :  09:42:32  Show Profile
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000