View Single Post
  #3 (permalink)  
Old December 15th, 2003, 12:54 PM
Jeff Mason Jeff Mason is offline
Friend of Wrox
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post

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