Circular JOIN - Backtracking in production-CodePudding

How to solve something like "circular JOIN" on table to solve this example of a production order backtracking problem?

The problem is:

I need to store evidence of production orders. During the production is usual that sometimes is need to split the order and continue in the production with the new order (with the relation on previous order).

See this example:

CREATE TABLE EventOrders
(
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    Code NVARCHAR(20),
    OrderTypeID INT
);

CREATE TABLE EventSetOrderRelations
(
    OrderRelationID INT IDENTITY(1,1) PRIMARY KEY,
    OrderIDIn INT FOREIGN KEY REFERENCES EventOrders(OrderID),
    OrderIDOut INT FOREIGN KEY REFERENCES EventOrders(OrderID)
);

INSERT INTO EventOrders (Code, OrderTypeID) VALUES
('221209-1',1),('221209-2',1),('221209-3-R',2),
('221209-4',1),('221209-5-R',2),('221209-6-R',2);

INSERT INTO EventSetOrderRelations (OrderIDIn, OrderIDOut) VALUES
(1,3),(2,5),(3,6);

SELECT * FROM EventOrders;
SELECT * FROM EventSetOrderRelations

OrderID	Code	OrderTypeID
1	221209-1	1
2	221209-2	1
3	221209-3-R	2
4	221209-4	1
5	221209-5-R	2
6	221209-6-R	2

OrderRelationID	OrderIDIn	OrderIDOut
1	1	3
2	2	5
3	3	6

Question:

I would like use some elegant query with parameter of the order (let's say that in the condition will be "Code" of the order) and get all previous orders in this branch...

My solution:

... I can solve this issue with this SQL script and the result is exactly what I need BUT I need to apply this solution in Stored Procedure...

DECLARE @MyCode NVARCHAR(20) = '221209-6-R'

DECLARE @CheckID INT
DECLARE @AllOrders TABLE (OrderID INT, Code NVARCHAR(20), OrderTypeID INT)
DECLARE @Count INT = 0

SET @CheckID = (SELECT OrderID FROM dbo.EventOrders WHERE Code = @MyCode)
-- --------------------------------------------------

WHILE @Count < 100
BEGIN
    SET @Count = @Count   1
  
    INSERT INTO @AllOrders (OrderID, Code, OrderTypeID)
    SELECT OrderID, Code, OrderTypeID FROM dbo.EventOrders
    WHERE OrderID = @CheckID

    SET @CheckID = (SELECT OrderIDIn FROM dbo.EventSetOrderRelations ESOR
    JOIN dbo.EventOrders EO ON EO.OrderID = ESOR.OrderIDOut
    WHERE EO.OrderID = (SELECT MIN(OrderID) FROM @AllOrders))

    IF @CheckID IS NULL
    BEGIN
        SET @Count = 100
    END
END

SELECT * FROM @AllOrders ORDER BY OrderID ASC

OrderID	Code	OrderTypeID
1	221209-1	1
3	221209-3-R	2
6	221209-6-R	2

My wonder:

I would like to ask You if is possible some easier way to get this result.

See DBFiddle:

fiddle link

CodePudding user response：

Seems that what you want is a recursive Common Table Expression (rCTE) here. You can pass the parameter for the order in the first query within the rCTE, and then use that data to recurse through the table(s):

WITH rCTE AS(
    SELECT EO.*
    FROM dbo.EventOrders EO
    WHERE EO.OrderID = @Order
    UNION ALL
    SELECT EO.*
    FROM dbo.EventOrders EO
         JOIN EventSetOrderRelations ESOR ON EO.OrderID = ESOR.OrderIDOut
         JOIN rCTE r ON ESOR.OrderIDIn = r.OrderID)
SELECT *
FROM rCTE;