View Single Post
  #1 (permalink)  
Old December 15th, 2003, 11:40 AM
owain owain is offline
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:
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?

Owain Williams
Owain Williams
Reply With Quote