Help with a query? Parent - Child hierarchy problem

Question

Help with a query? Parent - Child hierarchy problem

lewisdow123

Mr or Mrs. 500

Points: 562
More actions
May 24, 2012 at 2:43 am

#258624

Hi,
I'm hoping someone will be kind enough to give me a hand on a little problem I have.
Basically we a relationship table, that uses a parent-child association, this table is displayed in the code below as table #Old.
However I need to link the children together as shown in table #New.
The IDs will be random so it's not possible to just increment the id by one but it is most likely safe to say that the next id will be greater then the previous.
If someone could please show me a query method of getting from table #Old to table #New I would be really grateful.
Many thanks.
Lewis
create table #Old (FirstID int, SecondID int)
insert into #Old select 101, 201
insert into #Old select 101, 202
insert into #Old select 101, 203
insert into #Old select 101, 204
insert into #Old select 101, 205
select * from #Old
create table #New (ParentID int, FirstID int, SecondID int)
insert into #New select 101, 201, 202
insert into #New select 101, 202, 203
insert into #New select 101, 203, 204
insert into #New select 101, 204, 205
select * from #New
drop table #Old
drop table #New
May 24, 2012 at 3:45 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

lewisdow123 (5/24/2012)
Hi,
I'm hoping someone will be kind enough to give me a hand on a little problem I have.
Basically we a relationship table, that uses a parent-child association, this table is displayed in the code below as table #Old.
However I need to link the children together as shown in table #New.
The IDs will be random so it's not possible to just increment the id by one but it is most likely safe to say that the next id will be greater then the previous.
If someone could please show me a query method of getting from table #Old to table #New I would be really grateful.
Many thanks.
Lewis
create table #Old (FirstID int, SecondID int)
insert into #Old select 101, 201
insert into #Old select 101, 202
insert into #Old select 101, 203
insert into #Old select 101, 204
insert into #Old select 101, 205
select * from #Old
create table #New (ParentID int, FirstID int, SecondID int)
insert into #New select 101, 201, 202
insert into #New select 101, 202, 203
insert into #New select 101, 203, 204
insert into #New select 101, 204, 205
select * from #New
drop table #Old
drop table #New

You've got nothing there that determines the order of the "secondID". Physical ordering in the table doesn't count as SQL Server doesn't guarantee this.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

lewisdow123 Mr or Mrs. 500 Points: 562 More actions · Answer 2

The id is a primary key with automated self incrementing identity, so the next child will always be greater than the previous.

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

lewisdow123 (5/24/2012)
The id is a primary key with automated self incrementing identity, so the next child will always be greater than the previous.

That's not what you said in your original post.

Try this: -

SELECT a.FirstID AS ParentID, b.SecondID AS FirstID, a.SecondID

FROM #Old a

CROSS APPLY (SELECT TOP 1 SecondID

FROM #Old

WHERE FirstID = a.FirstID

AND SecondID < a.SecondID

ORDER BY SecondID DESC) b;

Produces: -

ParentID FirstID SecondID

----------- ----------- -----------

101 201 202

101 202 203

101 203 204

101 204 205

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

lewisdow123 Mr or Mrs. 500 Points: 562 More actions · Answer 4

Sorry for the confusion. That's very helpful, thank you.

I will try implementing this query in the actual query.

Thanks again

lewisdow123 Mr or Mrs. 500 Points: 562 More actions · Answer 5

Hi,

This worked but I have one question that perhaps you might be able to explain.

The #old table has 1000 + records, but when I implement your advice, the table returns only around 600 rows.

Any ideas why this may be?

Thank you,

Lewis

lewisdow123 Mr or Mrs. 500 Points: 562 More actions · Answer 6

lewisdow123

Mr or Mrs. 500

Points: 562

May 24, 2012 at 3:53 am

#1492364

Thanks to you both

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 7

not addressing the main point of the post, but in a parent/child hierarchical type query have you considered using the hierarchyid data type..?

i only say this because i saw code like this a few years go where there was some "logic flaw" that was never found - it allowed a junior employee to be the manager of the chairman of the company - thus creating an infinite loop of responsibility.

the hierarchy data type prevents all of that nonsence and gives really good features such as GetAncestor, GetRoot, and GetLevel (a really cool one) and ToString()

so rather than doing messy self joins to get parent and child info, you can do things like 'where hierarchyid.GetAncestor()='x'

MVDBA