Do you need a select?
I started to work on an update, but it might give you an idea.
CREATE TABLE #Order(
OrderID int CONSTRAINT PK_OrderID PRIMARY KEY,
SalesOrderID int,
OrderDate date,
OrderType varchar(20),
OrderStatus varchar(20)
);
CREATE TABLE #OrderDetails(
OrderDetailsID int CONSTRAINT PK_OrderDetailsID PRIMARY KEY,
OrderID int,
OrderStage varchar(20)
);
CREATE TABLE #RelatedOrderDetails(
OrderID int,--(FK to Order ID),
RelatedOrderDetailsID int,--(FK to Order Details ID),
RelatedOrderID int,--( FK to Order ID)
CONSTRAINT FK_OrderID FOREIGN KEY ( OrderID) REFERENCES #Order(OrderID),
CONSTRAINT FK_OrderDetailsID FOREIGN KEY ( RelatedOrderDetailsID) REFERENCES #OrderDetails(OrderDetailsID),
CONSTRAINT FK_RelatedOrderID FOREIGN KEY ( RelatedOrderID) REFERENCES #Order(OrderID)
);
INSERT INTO #Order(
OrderID ,
SalesOrderID,
OrderDate ,
OrderType
)
VALUES
(1, 1234, '2015-01-01', 'Refill'),
(2, 1234, '2015-02-02', 'Extension');
INSERT INTO #OrderDetails
VALUES
(1, 1, 'Approved'),
(2, 1, 'Approved'),
(3, 2, 'Rejected');
INSERT INTO #RelatedOrderDetails
VALUES
(2, 1, 1),
(2, 2, 1);
--Option 1
UPDATE o SET
OrderStatus = 'Approved'
FROM #Order o
JOIN #OrderDetails d ON o.OrderID = d.OrderID
LEFT
JOIN #RelatedOrderDetails r ON d.OrderID = r.OrderID
LEFT
JOIN #OrderDetails d2 ON d2.OrderID = r.RelatedOrderID
AND d2.OrderDetailsID = r.RelatedOrderDetailsID
WHERE d.OrderStage = 'Approved'
OR d2.OrderStage = 'Approved'
--Option 2
UPDATE o SET
OrderStatus = 'Approved'
FROM #Order o
WHERE EXISTS(
SELECT *
FROM #OrderDetails d
LEFT
JOIN #RelatedOrderDetails r ON d.OrderID = r.OrderID
LEFT
JOIN #OrderDetails d2 ON d2.OrderID = r.RelatedOrderID
AND d2.OrderDetailsID = r.RelatedOrderDetailsID
WHERE (d.OrderStage = 'Approved' OR d2.OrderStage = 'Approved')
AND o.OrderID = d.OrderID)
SELECT * FROM #Order;
GO
DROP TABLE #RelatedOrderDetails;
DROP TABLE #Order;
DROP TABLE #OrderDetails;