CODEWITHSUNDEEP
sqlsql-server
BrokeMyLegBiking
BrokeMyLegBiking

Reputation: 5988

Find all related records

I have an Order table that has a LinkedOrderID field.

I would like to build a query that finds all linked orders and returns them in the result set.

select OrderID,LinkOrderID from [Order] where LinkOrderID is not null

OrderID LinkOrderID
787016   787037
787037   787786
787786   871702


I would like a stored proc that returns the following:
OrderID InheritanceOrder
787016   1
787037   2
787786   3
871702   4

I would also like to make sure I don't have an infinite loop

Upvotes: 2

Views: 2363

Answers (3)

BrokeMyLegBiking
BrokeMyLegBiking

Reputation: 5988

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

Upvotes: 0

David Oneill
David Oneill

Reputation: 13065

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.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425341

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)

Upvotes: 3

Related Questions