Parent and Child Records in Same Table

owain · December 15th, 2003, 11:40 AM

I have a table that is constructed thus:

Code:

ID          int identity NOT NULL,
Parent      int          NULL,
Description varchar(100) NULL

The idea being that a record can be both a parent and a child of another record in the same table. For example I could have a record:

1, NULL, "I am a parent"

This record could then have a child record:

2, 1, "I am a child"

And then this child record could it's self have a child record:

3, 2, "I am a grand-child"

Each record can have more than one child record, and each child record can have multiple grand-child records, and each grand-child can have several great-grand-child records etc, etc. The records can nest several times (there is no limit).

Does anyone have any idea on how to construct a SQL query whereby I provide an ID number and all the records that link to that ID will be listed, no matter what the relation?

Regards
Owain Williams

planoie · December 15th, 2003, 11:47 AM

How far out do you want the data to go? One or many relationship steps? Do you want entire tree underneath those specified ID? Do you want all the ancestors in that ID's branch?

Peter
------------------------------------------------------
Work smarter, not harder.

Jeff Mason · December 15th, 2003, 12:54 PM

What you are describing looks like a hierarchy (or tree) to me. As you've discovered, it's not easy to represent and then traverse a tree in SQL. It's way too complex a subject to get into here. Get yourself a copy of "SQL for Smarties" by Joe Celko. He devotes a large chapter to the subject, and I understand he's about to publish an entire book on trees and hierarchies in SQL.

You might want to do some research into "nested sets" and/or "adjacency list". A Google search on these terms will point you in the right direction.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jonax · December 15th, 2003, 01:03 PM

I agree: Joe Celko is THE MAN!
There's an article by him on the very subject here: http://searchdatabase.techtarget.com...537290,00.html with a link to a follow-up.

owain · December 16th, 2003, 05:11 AM

Peter, yes I am afraid that I do want the entire tree underneath the specific ID along with all the ancestors.

Jeff, oh dear, and I was hoping someone would come up with a rather clever correlated sub-query that I couldn't think of, not a suggestion to read an entire book :). Arr well, back to the drawing board, I guess I will have to do it programmatically in my front end.

Jonas, thanks for the link. I have not had a chance to read it all yet but it looks like the sort of thing I am after.

Regards
Owain Williams

Jeff Mason · December 16th, 2003, 08:27 AM

Quote:

quote:..I was [not] hoping for ...a suggestion to read an entire book :)...

No no no. Not an entire book, just a chapter of one. ;) I don't think his entire book on the subject comes out until sometime in February.

The link to the article than Jonax gave you is more or less a substantial portion of the chapter he wrote anyway.

Do yourself a favor and get the "SQL for Smarties" book, whether its for the chapter on trees or not. Besides "Inside SQL Server", its the one book I refer to most often on the subject of SQL...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

pgtips · December 16th, 2003, 08:50 AM

This may not be the smartest way :D but you can use a temporary table and a loop to walk the tree.

Code:

CREATE PROCEDURE dbo.ListAllChildren
    @ParentID int
AS

CREATE TABLE #children
(
    [ID] int NULL,
    [Description] varchar(100) NULL,
    [Level] int NOT NULL
)

DECLARE @Level int
SET @Level = -1

INSERT #children
SELECT w.[ID], w.[Description], 0
FROM dbo.Wrox7599 w
WHERE w.[ID] = @ParentID

WHILE @@ROWCOUNT > 0
BEGIN
    SET @Level = @Level + 1

    INSERT #children
    SELECT w.[ID], w.[Description], @Level+1
    FROM dbo.Wrox7599 w
    WHERE w.Parent IN 
    (SELECT [ID] FROM #children
    WHERE [Level] = @Level)
END

SELECT * FROM #children

DROP TABLE #children

GO

pratapbhat · September 6th, 2004, 08:22 AM

ID int identity NOT NULL,
Parent int NULL,
Description varchar(100) NULL

if this is your table in oracle 9i there is a simple way of doing it

SELECT Description
    FROM your_table
    START WITH ID = 3
    CONNECT BY PRIOR PARENT = ID

owain · September 6th, 2004, 10:49 AM

Thanks for the tip, unfortunately I am not using Oracle, I am not even using SQL Server, I am trapped in the dark ages of Access, so even Phil's idea of using a temporary table doesn't help me. Oh how I long for a truly relational database management system :D.

Regards
Owain Williams

sal · September 9th, 2004, 03:24 PM

Just a tought, but pgtips is suggesting something that would work in Access if instead of inserting it into a temp table yoy insert it into a disconnected ADO recordset. Then you use the recordset for your form/report.

thas is the way I get my Tree traversed anyway. Hopefully MS will have a function like the one in Oracle some time soon.

Sal