trying to find the chain of orders using SQL query joining below two tables. If one order is dependent on two orders then the baseline order will be the one with minimum created_date.
Order table:
| order_id | product | created_date |
|---|---|---|
| 69980 | table | 11-12-2024 |
| 69981 | stool | 11-15-2024 |
| 69982 | phone | 11-23-2024 |
| 73396 | car | 10-11-2024 |
| 73395 | bike | 11-16-2024 |
| 73397 | door | 11-17-2024 |
Connection Table:
| order_id | connection_id |
|---|---|
| 69980 | |
| 69981 | 69982 |
| 69981 | 69980 |
| 69982 | 69981 |
| 73395 | |
| 73396 | |
| 73397 | 73395 |
| 73397 | 73396 |
Expected data set:
| order_id | connection_id | baseline |
|---|---|---|
| 69980 | 69981 | 69980 |
| 69981 | 69980 | 69980 |
| 69981 | 69982 | 69980 |
| 69982 | 69981 | 69980 |
| 73396 | null | 73396 |
| 73395 | null | 73395 |
| 73397 | 73395 | 73396 |
| 73397 | 73396 | 73396 |
baseline for 73397 is 73396 base we need to pull the connection with least created_date from connection table.
Query ran –
with order as (
select
order_id
from
order ord
),
connection as (
SELECT
con.order_id,
con.connection_id AS connect_order,
ord.order_id
FROM
connection con
join order ord on ord.order_id = con.order_id
UNION
SELECT
con.connection_id,
order_id AS connect_order,
ord.order_id
FROM
connection con
join order ord on ord.order_id = con.connection_id
)
select distinct
ord.order_id,
con.order_id,
con.linked_orders
from
order ord
left join connection con on ord.order_id = con.order_id
4
You can use a correlated heierarchical sub-query:
SELECT c.order_id,
c.connection_id,
( SELECT x.order_id
FROM connection x
INNER JOIN orders o
ON x.order_id = o.order_id
START WITH c.order_id = x.order_id
CONNECT BY NOCYCLE
PRIOR x.connection_id = x.order_id
ORDER BY o.created_date ASC
FETCH FIRST ROW ONLY
) AS baseline
FROM connection c
Which, for the sample data:
CREATE TABLE Orders (order_id, product, created_date) AS
SELECT 69980, 'table', DATE '2024-11-12' FROM DUAL UNION ALL
SELECT 69981, 'stool', DATE '2024-11-15' FROM DUAL UNION ALL
SELECT 69982, 'phone', DATE '2024-11-23' FROM DUAL UNION ALL
SELECT 73396, 'car', DATE '2024-10-11' FROM DUAL UNION ALL
SELECT 73395, 'bike', DATE '2024-11-16' FROM DUAL UNION ALL
SELECT 73397, 'door', DATE '2024-11-17' FROM DUAL;
CREATE TABLE Connection (order_id, connection_id) AS
SELECT 69980, NULL FROM DUAL UNION ALL
SELECT 69981, 69982 FROM DUAL UNION ALL
SELECT 69981, 69980 FROM DUAL UNION ALL
SELECT 69982, 69981 FROM DUAL UNION ALL
SELECT 73395, NULL FROM DUAL UNION ALL
SELECT 73396, NULL FROM DUAL UNION ALL
SELECT 73397, 73395 FROM DUAL UNION ALL
SELECT 73397, 73396 FROM DUAL;
Outputs:
| ORDER_ID | CONNECTION_ID | BASELINE |
|---|---|---|
| 69980 | null | 69980 |
| 69981 | 69982 | 69980 |
| 69981 | 69980 | 69980 |
| 69982 | 69981 | 69980 |
| 73395 | null | 73395 |
| 73396 | null | 73396 |
| 73397 | 73395 | 73396 |
| 73397 | 73396 | 73396 |
fiddle
2