I have two tables, 'Orders' and 'Returns', both of which have primary key column 'ID' (auto-generated into identity).
The 'Orders' table also has a nullable 'ReturnID' column to store the ID of a parent Return record if one exists and likewise the 'Returns' table has a 'OrderID' column to store the the ID of a parent Order record if one exists.
My question is, how can I query this structure to show a hierarchy? The system in question allows the user to enter either a return or order as a starting point and from that point any number of return/replacement orders can be entered that originate from the original order/return record. My eventual goal is to represent this in a hierarchy within the app but I'm struggling on the SQL side a little at the moment.
Orders (OrderID), Returns (ReturnID, OrderID)
You say the returns table might not have an OrderID - how is that possible?
How is a replacement order referenced (Orders table, Returns table, another table?) - Maybe there is a replacement order ID on the Returns table?
Orders (OrderID), Returns (ReturnID, OrderID, NewOrderID)
So how do you want the structure returned to the application.
I would also expect something to show which was the first order e.g. a sequence number or date or the original OrderID in the sequence on one of the tables.
I'm assuming the OrderID in the Returns table is unique.
It can be a wide dataset with a series of OrderID, ReturnID but you would need to specify the number of columns needed.
It could be
SeqNum, InputID, InputIDType, OrderID, ReturnID
Then you would have a series of rows showing the history of the order.
That would be something like this
;with cte as
(
' get an OrderID
select Top 1 OrderID from Returns where ReturnID = @ID and @Type = 'Return'
union all
select @ID where type = 'Order'
)
, cte1 as
(
' get first order - have to traverse up the hierarchy if nothing else available
select OrderID, seq = 1 from cte
union all
select OrderID = t2.OrderID, seq = seq + 1 from cte t1 join Returns t2 on t2.NewOrderID = t1.OrderID
)
, cte2 as
(
' get first OrderID
select top 1 OrderID from cte1 order by seq desc
)
, cte3 as
(
' get order hierarchy
select OrderID, ReturnID = t2.ReturnID, t2.NewOrderID, seq = 1 from cte2 t1 left join Returns t2 on t1.OrderID = t2.OrderID
union all
select t1.NewOrderID, t2.ReturnID, t2.NewOrderID, seq = seq + 1 from cte3 t1 join Returns t2 on t1.NewOrderID = t2.OrderID
)
select InpID = @ID, InpType = @Type, Order = OrderID, Return = ReturnID, seq
from cte3
order by seq
To answer your questions. A return in this particular environment can exist without an order as a starting point, this is in the case where the order details cannot be found due to the age, legacy systems etc. Regarding how I want this to be returned. The user can select any order or return, from which I want to be able to produce a result set showing the entire transactional history related to the selected item, right back to the very first related order/return down to the last order/return. I would require some kind of 'level' value included in the result set so that I can use it to bind/indent on my display e.g. using a treeview. The idea is that for any return or order selected, the team will be able to view the entire history for the transaction. There is no limit to the number of levels as a customer can go through multiple cycles of orders/returns, particularly in the case of lifetime guarantees.
Finally, multiple returns can exist against a single order, i.e. customer purchases multiple products on order, they return 1 item after 12 months and a 2nd item after 18 months.