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?