Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2008
    Location
    Denver, CO
    Posts
    44

    Unanswered: Getting all related records from table that is linked to itself

    Is it possible to retrieve all of the related records in a table that is linked to itself with an associative table. For example, here is some (pseudoish) DDL for creating this structure:

    CREATE TABLE table1 (ID INT IDENTITY(1,1), Name VARCHAR(25))

    CREATE TABLE parent_child (ParentID INT REFERENCES table1(ID), ChildID INT REFERENCES table1(ID), CONSTRAINT ParentID, ChildID PRIMARY KEY)

    Let's say that I have many records that are linked in a parent/child relationship, and I want to retrieve all of the records that are in a particular parent/child hierarchy, how would I write this DML statement?
    Paul Palubinski

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You could use a recursive CTE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •