I am trying to write a SQL that retrieves parent-child relationships from the product data, including a calculated Consolidated Quantity. The consolidated quantity is derived by multiplying the child’s quantity by the parent’s quantity recursively, considering multiple levels of hierarchy.
Table Structure:
Product Table:
ID: Primary key, unique identifier for the product.
Name: The name of the product.
Relationships Table:
Product_ID: Foreign key, referencing the Product.ID field (parent product).
Child_Product_ID: Foreign key, referencing the Product.ID field (child product).
Child_Product_name: The name of the child product.
QTY: The quantity of the child product needed for the parent product.
Example Case:
Parent A has two child products:
B1 with quantity 2
B2 with quantity 3
B1 has a child C1 with quantity 1
B2 has a child C2 with quantity 2
The output should be:
A, B1, 2
A, B2, 3
A, C1, 2 (Consolidated quantity: 2 B1 * 1 C1)
A, C2, 6 (Consolidated quantity: 3 B2 * 2 C2)
Same SQL
-- Create Product Table
CREATE TABLE Product (
ID NUMBER(3),
Name VARCHAR2(100)
);
-- Create Relationships Table
CREATE TABLE Relationships (
Product_ID NUMBER(3),
Child_Product_ID NUMBER(3),
Child_Product_Name VARCHAR2(100),
QTY NUMBER(10)
);
-- Insert into Product Table with 3-digit IDs
INSERT INTO Product (ID, Name) VALUES
(100, 'A'), -- A
(200, 'B1'), -- B1
(300, 'B2'), -- B2
(400, 'C1'), -- C1
(500, 'C2'); -- C2
-- Insert into Relationships Table with Child Product Name
INSERT INTO Relationships (Product_ID, Child_Product_ID, Child_Product_Name, QTY) VALUES
(100, 200, 'B1', 2), -- A needs 2 B1
(100, 300, 'B2', 3), -- A needs 3 B2
(200, 400, 'C1', 1), -- B1 needs 1 C1
(300, 500, 'C2', 2); -- B2 needs 2 C2
Output
I tried writing recursive queries but did not get the desired output
2
Use a recursive query and then JOIN to the Product table to get the names:
WITH hierarchy (product_id, child_product_id, qty) AS (
SELECT product_id, child_product_id, qty
FROM relationships
WHERE Product_id = 100
UNION ALL
SELECT r.product_id, r.child_product_id, h.qty * r.qty
FROM hierarchy h
INNER JOIN relationships r
ON h.child_product_id = r.product_id
)
SELECT p.name,
c.name AS child_name,
h.qty
FROM hierarchy h
INNER JOIN Product P ON h.product_id = p.id
INNER JOIN Product C ON h.child_product_id = c.id
Which, for the sample data, outputs:
| NAME | CHILD_NAME | QTY |
|---|---|---|
| A | B1 | 2 |
| A | B2 | 3 |
| B1 | C1 | 2 |
| B2 | C2 | 6 |
fiddle