Finding related rows in a Parent/Child relationshi

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Finding related rows in a Parent/Child relationshi

Author

Topic

Cralis
Starting Member

11 Posts

Posted - 2010-01-18 : 20:51:38

Hi guys.

I need to find all related rows in a query, in a parent child relationship.

Here's some DDL. Basically, I need to find all the rows related to, for example, id=3.

The result should end up being 1,2,3,4 & 8, which are all linked (Because 2 is related to 1, via the parent ID, 3 is related to 2, 4 is related to 3, and 8 is related to 4. But I'm battling to find an efficient query to do so. Could someone assist?

CREATE TABLE Relationship
(
id INT NOT NULL,
parent_id INT NULL
)

INSERT INTO Relationship
(id, parent_id)
VALUES
(1, NULL)

INSERT INTO Relationship
(id, parent_id)
VALUES
(2, 1)

INSERT INTO Relationship
(id, parent_id)
VALUES
(3, 2)

INSERT INTO Relationship
(id, parent_id)
VALUES
(4, 3)

INSERT INTO Relationship
(id, parent_id)
VALUES
(5, NULL)

INSERT INTO Relationship
(id, parent_id)
VALUES
(6, 5)

INSERT INTO Relationship
(id, parent_id)
VALUES
(7, 6)

INSERT INTO Relationship
(id, parent_id)
VALUES
(8, 4)

DROP TABLE Relationship

amerbashoeb
Starting Member

14 Posts

Posted - 2010-01-18 : 21:02:36

checkout this link. Hope it helps.. cheers
http://www.webinade.com/web-development/creating-recursive-sql-calls-for-tables-with-parent-child-relationships

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources