Parent Child Link

Question

Post reply

Parent Child Link

David-155102

SSCarpal Tunnel

Points: 4361
More actions
October 9, 2008 at 12:43 am

#194108

I posted a question a couple of years ago about the best way to find parent and child records (original post http://www.sqlservercentral.com/Forums/Topic263741-8-1.aspx), does anyone know if there is anything in SQL Server 2005 that can help?
Thanks
David

Viewing 11 posts - 1 through 10 (of 10 total)

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

ChiragNS

One Orange Chip

Points: 26137

October 9, 2008 at 12:56 am

#882710

CTE's.

"Keep Trying"

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hi David, there are established methods for achieving this. Some sample data would help tremendously. Here's a start though I'm guessing somewhat, please can you correct the data to fit your requirements?

Cheers

ChrisM

CREATE TABLE #TableA ([ID] VARCHAR(4))

INSERT INTO #TableA ([ID])

SELECT '100' UNION ALL

SELECT '101' UNION ALL

SELECT '102'

CREATE TABLE #TableB ([ID] VARCHAR(4), PID VARCHAR(4))

INSERT INTO #TableB ([ID], PID)

SELECT '101', '100' UNION ALL

SELECT '102', '100' UNION ALL

SELECT '103', '100' UNION ALL

SELECT '101A', '101' UNION ALL

SELECT '102A', '101' UNION ALL

SELECT '103A', '101' UNION ALL

SELECT '102A', '102'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 3

Hi Chris,

Here is a sample script that will replicate a portion of my data:

CREATE TABLE #tblTMembers(

[CHILD_ID] [int] NOT NULL,

[PARENT_ID] [int] NULL

) ON [PRIMARY]

INSERT INTO #tblTMembers

([CHILD_ID]

,[PARENT_ID])

SELECT 2,80000551 UNION ALL

SELECT 5,80000551 UNION ALL

SELECT 7,80000551 UNION ALL

SELECT 8,80000551 UNION ALL

SELECT 9,80000551 UNION ALL

SELECT 10,80000551 UNION ALL

SELECT 11,80000551 UNION ALL

SELECT 12,80000551 UNION ALL

SELECT 13,80000551 UNION ALL

SELECT 14,80000551 UNION ALL

SELECT 36,80000551 UNION ALL

SELECT 50000001,80000551 UNION ALL

SELECT 80000503,80000551 UNION ALL

SELECT 80000289,2 UNION ALL

SELECT 80000291,2 UNION ALL

SELECT 10353,80000289 UNION ALL

SELECT 10354,80000289 UNION ALL

SELECT 10355,80000289 UNION ALL

SELECT 10356,80000289 UNION ALL

SELECT 10357,80000289 UNION ALL

SELECT 10358,80000289 UNION ALL

SELECT 10359,80000289 UNION ALL

SELECT 14562,80000289 UNION ALL

SELECT 10351,80000291 UNION ALL

SELECT 10352,80000291 UNION ALL

SELECT 80000478,80000291 UNION ALL

SELECT 80000551, NULL

The member 80000551 is the top of the tree and it has child members 2, 5, 7, 8, 9, 10, 11, 12, 13, 14, 36 and 80000503. Member 2 is also a parent and has child members 80000289 and 80000291.

Member 80000291 is also a parent and has child members 10351, 10352, 80000478 and 80000609.

The problem I have is that some member may or may not be parents themselves and it is this that I want to try and get around. For example, member 10351 is not a parent but in the future it may so my script needs to be dynamic enough to be able to detect any new child members as and when they appear. The other thing to mention is that the structure of the data may not always be the same for each parent, e.g. member 80000551 may have a child member that is not a parent.

Thanks

David

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Hi David, many thanks for posting this. The next question would be...how many levels deep can this hierarchy go? If it's fixed at say 2 or 3, then the solution would be different to "unknown" or "multiple".

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 5

Hi Chris,

The problem is that the number of levels is not fixed, for some members it could be two levels deep, for others three and for others one!

Thanks

David

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Is there a logical maximum?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 7

I can't ever see it going beyond six levels if that helps!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Hi David

With six node levels and a remote possibility that it could increase, your best bet is almost certainly a recursive CTE as described in the following article:

http://www.sqlservercentral.com/articles/T-SQL/2926/

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 9

Hi Chris,

Ok, I'll have a read, thanks for your help.

Regards

David

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 10

Just an update, in case anyone else is reading this, I based my query upon the example in the link that Chris sent as follows:

with AllNodes (parent_id, child_id, level)

as

(

select parent_id, child_id, 0 as level

from tblTMembers

where parent_id = 80000551

union all

select a.parent_id, a.child_id, b.level + 1

from tblTMembers as a

inner join AllNodes b on a.parent_id = b.child_id

)

select parent_id, child_id, level

from AllNodes

order by level

and it seems to do the trick! I need to test this on real data but that won't be until I get back to work.

Thanks again for your help Chris.