Find all related records

Answer 1

DECLARE @Order TABLE (OrderID INT NOT NULL, LinkOrderID  INT NOT NULL)

INSERT
INTO    @Order
VALUES  (787016, 787037)

INSERT
INTO    @Order
VALUES  (787037, 787786)

INSERT
INTO    @Order
VALUES  (787786, 871702)

/*
INSERT
INTO    @Order
VALUES  (871702, 787016)
*/

;WITH    q (OrderId, LinkOrderId, InheritanceOrder, FirstItem) AS
        (
        SELECT  OrderID, LinkOrderId, 1, OrderID
        FROM    @Order
        WHERE   OrderID = 787786
        UNION ALL
        SELECT  o.OrderId, o.LinkOrderId, q.InheritanceOrder + 1, q.FirstItem
        FROM    q
        JOIN    @Order o
        ON      o.OrderID = q.LinkOrderId
        WHERE   o.OrderID <> q.FirstItem
        UNION ALL
        SELECT  LinkOrderId, NULL, q.InheritanceOrder + 1, q.FirstItem
        FROM    q
        WHERE   q.LinkOrderID NOT IN
                (
                SELECT  OrderID
                FROM    @Order
                )
        )
SELECT  OrderID, InheritanceOrder
FROM    q
ORDER BY
        InheritanceOrder

This assumes that both OrderID and LinkOrderID are unique (i. e. it's a linked list, not a tree).

Works with the last insert uncommented too (which makes a loop)

Answer 2

To check for an infinite loop, there are two checks:

First, make sure that you start with an _id that ever appears in the LinkOrderID

select o1.OrderID from Order o1
left outer join Order o2 on o1.OrderId = o2.LinkOrderID
where o2.LinkOrderID is null;

This will give you a list that are the starts of your linked lists.

Then, make sure none of your _ids ever show up more than once.

select * from {
  select LinkOrderId, count(*) as cnt from Order
} where cnt > 1;

If these two conditions are true (you are starting from an order that never appears in the Linked list and you have no OrderIds that are linked multiple times) then you can't have a loop.

Answer 3

Sweet: i found the solution thanks to Quassnoi's awesome code. I tweaked it to first walk up to the oldest parent, and then walk down through all children. Thanks again!

-- =============================================
-- Description: Gets all LinkedOrders for OrderID. 
-- It will first walk up and find oldest linked parent, and then next walk down recursively and find all children.
-- =============================================
alter PROCEDURE Order_Order_GetAllLinkedOrders
(
    @StartOrderID int
)
AS
--Step#1: find oldest parent
DECLARE @oldestParent int
;WITH    vwFirstParent (OrderId) AS 
        ( 
        SELECT  OrderID
        FROM    [Order]
        WHERE   OrderID = @StartOrderID 
        UNION ALL 
        SELECT  o.OrderId
        FROM    vwFirstParent
        JOIN    [Order] o 
        ON      o.LinkOrderID = vwFirstParent.OrderId 
        )

select @oldestParent = OrderID from vwFirstParent 

--Step#2: find all children, prevent recursion
;WITH    q (OrderId, LinkOrderId, InheritanceOrder, FirstItem) AS 
        ( 
        SELECT  OrderID, LinkOrderId, 1, OrderID 
        FROM    [Order]
        WHERE   OrderID = @oldestParent 
        UNION ALL 
        SELECT  o.OrderId, o.LinkOrderId, q.InheritanceOrder + 1, q.FirstItem 
        FROM    q 
        JOIN    [Order] o 
        ON      o.OrderID = q.LinkOrderId 
        WHERE   o.OrderID <> q.FirstItem 
        UNION ALL 
        SELECT  LinkOrderId, NULL, q.InheritanceOrder + 1, q.FirstItem 
        FROM    q
        WHERE   q.LinkOrderID NOT IN 
                ( 
                SELECT  OrderID 
                FROM    [Order]
                )
        ) 
SELECT  OrderID,LinkOrderId,  InheritanceOrder
FROM    q