So I've been learning how to do this and have a proof of concept but I'm not sure how to apply it for use.
I have a parent_order_id and an order_id. Each parent_order_id can also have a parent_order_id. Problem is I don't know how far back this could go. In the end I just want to know what the original order was. Using the code below gets me the answers, but I'd rather see each order and associated parent in separate columns, and next to each other so I can see the trail instead of seeing it as the code below provides. So if there were 4 orders I'd see:
Parent Order Order Order
instead of below:
Any help appreciated.
Code result ex:
| LEVEL |
PARENT_ORDER_ID |
ORDER_ID |
| 3 |
00706 |
00207 |
| 2 |
00604 |
00807 |
| 2 |
00207 |
00208 |
| 2 |
01807 |
02705 |
| 2 |
02007 |
00905 |
| 2 |
NULL |
01407 |
| 1 |
01304 |
01101 |
| 1 |
01608 |
05205 |
| 1 |
02204 |
05304 |
| 1 |
35950 |
02504 |
| 1 |
31150 |
04500 |
CODE
WITH PARENT_ORDER_CTE AS (
Select
PARENT_ORDER_ID
, ORDER_ID
, 1 AS level
from ORDER O
JOIN PARENT I on O.ORDER_ID = I.ORDER_ID
where PARENT_ORDER_ID is not null
UNION ALL
SELECT
i.PARENT_ORDER_ID
, o.ORDER_ID
, level + 1
from ORDER O
JOIN PARENT I on O.ORDER_ID = I.PARENT
INNER JOIN PARENT_ORDER_CTE PO
ON o.order_ID = po.PARENT_ORDER_ID
)
SELECT DISTINCT LEVEL, PARENT_ORDER_ID, ORDER_ID
FROM PARENT_ORDER_CTE
ORDER BY ORDER_ID
It would be helpful if you could provide DDL/CREATE TABLE statements for your tables, plus some INSERT statements with sample data, and lastly some expected results based on that sample data (this can be tabular like your current example)
Currently there's only 1 Order_ID in the sample that is both parent and child, and not enough data to enumerate 3 levels as your sample suggests.
Some other questions/concerns:
-
Is this for a SQL Server database? I can't parse your SQL statement, the table ORDER is a reserved word and would require delimiters to be processed correctly. (SQLTeam is a Microsoft SQL Server site, we're not experts on other platforms)
-
If a parent order_id has multiple children, e.g. 1->2->4 and 1->3->5, would you want them to display on separate rows? Then there's multiple grandchildren (1->2->4, 1->2->6, 1->3->5, 1->3->7). These represent independent lineages that can only be flattened independently.
-
I'm curious about the SELECT DISTINCT in the final SELECT statement. Are you getting duplicate rows without it? That could indicate an underlying data integrity issue, i.e. no primary key or uniqueness on order_id. Having the table DDL would hopefully answer this.
-
If you actually require the descendant order IDs in separate columns, you'll end up with a dynamic PIVOT query, which is only possible in SQL Server using dynamic SQL. Unless you know you'll only ever have a certain depth, like 4 or 5, which can be hardcoded. A breadcrumb representation (a->b->c->d) can be done more easily using the STRING_AGG() function, or another equivalent.
1 Like