Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 15th, 2003, 11:40 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default Parent and Child Records in Same Table

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
__________________
Regards
Owain Williams
Reply With Quote
  #2 (permalink)  
Old December 15th, 2003, 11:47 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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.
Reply With Quote
  #3 (permalink)  
Old December 15th, 2003, 12:54 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #4 (permalink)  
Old December 15th, 2003, 01:03 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , Denmark.
Posts: 184
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Jonax
Default

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.

Reply With Quote
  #5 (permalink)  
Old December 16th, 2003, 05:11 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #6 (permalink)  
Old December 16th, 2003, 08:27 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #7 (permalink)  
Old December 16th, 2003, 08:50 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #8 (permalink)  
Old September 6th, 2004, 08:22 AM
Registered User
 
Join Date: Sep 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old September 6th, 2004, 10:49 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #10 (permalink)  
Old September 9th, 2004, 03:24 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Into Parent Child Table prasanta2expert Access VBA 3 November 8th, 2007 11:18 AM
parent child connect s2mo SQL Server 2005 1 February 20th, 2007 07:16 AM
Parent - Child Combo babloo81 BOOK: Professional Jakarta Struts 0 April 27th, 2005 01:54 PM
Parent - Child Combo babloo81 JSP Basics 0 April 27th, 2005 01:46 PM
Update parent table with the sum of child table gbrown SQL Language 2 November 9th, 2004 07:53 AM



All times are GMT -4. The time now is 10:23 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.