How to get all children in a self-reference table

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)

How to get all children in a self-reference table

Author

Topic

lianaent
Starting Member

5 Posts

Posted - 2008-04-15 : 18:38:40

I struggled with this for several days, and could find no help online for this particular issue. I figured out the answer finally and thought I'd share it.

The problem is how do you return all children and their siblings from a self-referencing table given a parent id. It turns out it's really simple using recursion. It may be simpler in SQL 2005 or 2008, but I don't have those.

CREATE FUNCTION f_GetChildren (@ParentId int)

RETURNS @tblTree TABLE
(
ParentId int,
ChildId int
)
AS
BEGIN
DECLARE @tblChildren TABLE
(
rownum int identity(1,1) NOT NULL,
ParentId int,
ChildId int
)

INSERT @tblChildren
SELECT ParentId, ChildId
FROM MainTable
WHERE ParentId = @ParentId

DECLARE @rownum int
SET @rownum = 1
DECLARE @MaxRec int
SET @MaxRec = (SELECT count(*) FROM @tblChildren)
DECLARE @ChildId int

WHILE @rownum <= @MaxRec
BEGIN
SET @ChildId = (SELECT ChildId FROM @tblChildren WHERE rownum = @rownum)

INSERT @tblTree
SELECT ParentId, ChildId
FROM @tblChildren
WHERE rownum = @rownum
UNION
SELECT ParentId, ChildId
FROM f_GetChildren(@ChildId)

SET @rownum = @rownum + 1
END

RETURN
END

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-16 : 10:01:11

Looks like an interesting (and fun) problem. Could you explain it a bit more? Is this literally parents and children, or just metaphorically? Can children have > 1 parent? Can children have > 2 parents?

Some sample data and an example would be great.

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-04-16 : 10:10:42

Have a look at this link:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=2828

Topic is old but it still applies. Check the two links on the bottom.

--
Lumbago

lianaent
Starting Member

5 Posts

Posted - 2008-04-16 : 11:06:56

Thanks Ryan,

Actually, it was fun!

Sorry for the lack of example, but here's a simple one. Say you have a table like (lousy naming convention I know, but I haven't had my morning coffee yet, and you get the idea):

ChildId ChildName ParentId
1 P1 0
2 P2 0
3 P1C1 1
4 P1C2 1
5 P2C1 2
6 P1C2C1 4
7 P1C1C1 3
8 P1C1C1C1 7
9 P2C1C1 5
10 P1C3 1

Let's say you want to return the entire P1 family tree. P1's ChildId is 1, so look for any ParentId's of 1 - P1C1 (3), P1C2 (4), P1C3 (10). P1C1 is in turn a child of P1C1C1 (7) which is a child of P1C1C1C1 (8). And so on.

When you run the above function:

select * from f_getchildren(1)

You get:

ParentId ChildId
1 3
3 7
7 8
1 4
4 6
1 10

Of course you can start anywhere in the tree:

select * from f_getchildren(3)

ParentId ChildId
3 7
7 8

I went through a ton of very complex code samples, some using .Net, some saying it couldn't be done. But the answer is so simple I felt I could save others a lot of digging.

Of course you can then use this function from a stored procedure to return the ChildName and any other info and return it to your front end. For example, the following procedure returns all the data in the table for the Parent P1 in generational order (first generation hits first, then all second generation hits, etc.)

ALTER PROCEDURE dbo.GetChildren
@ParentId int

AS

SET NOCOUNT ON

SELECT *
FROM MainTable
WHERE ChildId IN
(
SELECT ParentId
FROM f_GetChildren(@ParentId)
UNION
SELECT ChildId
FROM f_GetChildren(@ParentId)
)
ORDER BY ParentId

SET NOCOUNT OFF
RETURN

returns:

ChildId ChildName ParentId
1 P1 0
3 P1C1 1
4 P1C2 1
10 P1C3 1
7 P1C1C1 3
6 P1C2C1 4
8 P1C1C1C1 7

Hope that clarifies it!

Larry

lianaent
Starting Member

5 Posts

Posted - 2008-04-16 : 11:17:37

Thanks Lumbago,

I didn't look at all those solutions in detail, but like I said, they all look more complex or less intuitive, and some don't seem to return tables that I can see.

Larry

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-16 : 11:40:53

So there's only one parent, per child? In that case, I find it easiest to understand and work with if you create a function which returns the 'path' of ancestors.

To illustrate it with your example, it's something like this. Note the AncestorPath column in the results at the end.

-- Structure
create table dbo.MyPeople (Id int, Name varchar(30), ParentId int)

-- Data
insert dbo.MyPeople
          select 1, 'P1', null
union all select 2, 'P2', null
union all select 3, 'P1C1', 1
union all select 4, 'P1C2', 1
union all select 5, 'P2C1', 2
union all select 6, 'P1C2C1', 4
union all select 7, 'P1C1C1', 3
union all select 8, 'P1C1C1C1', 7
union all select 9, 'P2C1C1', 5
union all select 10, 'P1C3', 1
go

-- Function
create function dbo.AncestorPath(@Id int) returns varchar(100) as
begin
    declare @Path varchar(100)
    while 0 = 0
    begin
        select @Path = cast(Id as varchar(5)) + isnull('/' + @Path, ''), @Id = ParentId
        from dbo.MyPeople where Id = @Id

        if @@rowcount = 0 break
    end

    return @Path
end
go

-- Calculation
select * from (
    select *, dbo.AncestorPath(Id) as AncestorPath from dbo.MyPeople) a
where '/' + AncestorPath + '/' like '%/1/%'

/* Results
Id          Name                           ParentId    AncestorPath
----------- ------------------------------ ----------- -------------
1           P1                             NULL        1
3           P1C1                           1           1/3
4           P1C2                           1           1/4
6           P1C2C1                         4           1/4/6
7           P1C1C1                         3           1/3/7
8           P1C1C1C1                       7           1/3/7/8
10          P1C3                           1           1/10
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources