Child parent in one rowset... PLEASE HELP!

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

SQL Server 2000 Forums

Transact-SQL (2000)

Child parent in one rowset... PLEASE HELP!

Author

Topic

mori0043
Starting Member

18 Posts

Posted - 2006-08-23 : 13:12:55

Hello, I have a table where child and parent information reside. A child row will have a parentKey referring to it's parent's (client key) and a parent will have a parentKey of 0:

Table Clients
-----------
ClientKey
ParentKey
Description

How do I query this table to return parents and clients ordered correctly so it is:

ClientKey-01 ParentKey-00 Description:Parent1
ClientKey-02 ParentKey-01 Description:Child1 of Parent1
ClientKey-03 ParentKey-01 Description:Child2 of Parent1
ClientKey-04 ParentKey-00 Description:Parent2
ClientKey-05 ParentKey-04 Description:Child1 of Parent2

Thanks in advance!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-08-23 : 13:28:06

This should work.

Select * From yourTabble order by isnull(nullif(parentKey,'00'),ClientKey), ClientKey

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-08-23 : 13:32:04

tested:




Declare @t Table (
	ClientKey int,
	ParentKey int,
	descStr varchar(20)
)

Insert Into @t Select 1,0, 'Parent 1'
Insert Into @t Select 2,0, 'Parent 2'
Insert Into @t Select 3,2, 'Child 1 of Parent 2'
Insert Into @t Select 4,1, 'Child 1 of Parent 1'
Insert Into @t Select 5,2, 'Child 2 of Parent 2'
Insert Into @t Select 6,1, 'Child 2 of Parent 1'

Select * From @t order by isnull(nullif(parentKey,0),ClientKey), ClientKey

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

mori0043
Starting Member

18 Posts

Posted - 2006-08-23 : 13:53:30

Thank you sooooo much! This is such a help and I couldn't figure it out for the life of me!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-08-23 : 13:59:41

I should point out that this will only work as long as the clientKey and the ParentKey are in the correct numerical order... for example if the data showed:

Insert Into @t Select 1,0, 'Parent 1'
Insert Into @t Select 2,0, 'Parent 2'
Insert Into @t Select 3,1, 'Child 1 of Parent 1'
Insert Into @t Select 4,2, 'Child 2 of Parent 2'
Insert Into @t Select 5,1, 'Child 2 of Parent 1'
Insert Into @t Select 6,2, 'Child 1 of Parent 2'

you will notice that the 'Children of Parent 2' are out of order.

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources