Incorporating a recursive CTE result into a secondary SQL Select

Question

I have a complex SQL query that successfully pulls out the cost of component product records and calculates an overall cost for a parent/bundle product. This works when each of the components has a supplier cost and is not a parent/bundle product itself.

Query #1

SET @parentid = 36;
SELECT
    sub.product_sku AS product_sku,
    sub.product_label AS product_label,
    c2p.bundle_parentid AS bundle_parentid,
    c2p.componentid AS comp_product_id,
    sub.qty AS qty,
    sub.preferred AS preferred,
    sub.supply_qty AS supply_qty,
    sub.cost AS cost,
    ROUND((SELECT cost)/(SELECT supply_qty),2) AS adjusted_cost
    FROM products AS p
    JOIN component2bundle AS c2p ON c2p.componentid = p.product_id
    JOIN (
        /* Get the preferred/cheapest supplier date for this component */
        SELECT
            p2.product_sku AS product_sku,
            p2.product_label AS product_label,
            IFNULL(s2p2.cost, NULL) AS cost,
            s2p2.productid AS product_id,
            s2p2.supplier_preferred AS preferred,
            s2p2.product_quantity AS supply_qty,
            c2p2.componentid AS comp_product_id,
            c2p2.component_quantity AS qty,
            c2p2.bundle_parentid AS bundle_parentid
            FROM products AS p2
                INNER JOIN supplier2product AS s2p2 ON s2p2.productid = p2.product_id
                INNER JOIN component2bundle AS c2p2 ON s2p2.productid = c2p2.componentid
            WHERE c2p2.bundle_parentid = @parentid
                 AND c2p2.c2p_archive = 0
                AND COALESCE(s2p2.s2p_archive,0) = 0
            ORDER BY c2p2.componentid ASC, s2p2.supplier_preferred DESC, s2p2.cost ASC
    ) AS sub
    ON (sub.product_id = c2p.componentid)
    WHERE c2p.bundle_parentid = @parentid;

My aim is to adapt or rewrite the query so that it can pull out the costs of any components that are bundles so it seems a recursive CTE query is the way to proceed.

I have successfully written a CTE query that can pull each of the component product id's out of a table that shows the parent -> child relationship and it assigns each a level in the hierarchy. What I'm struggling with is how to integrate the two.

CTE Query

WITH RECURSIVE components AS 
(
    SELECT componentid, 1 AS level
    FROM component2bundle
    WHERE bundle_parentid = 'target_productID'
    UNION ALL
        SELECT c2b.componentid, c.level+1
        FROM components c, component2bundle c2b
        WHERE c2b.bundle_parentid = c.componentid
)
SELECT * FROM components ORDER BY level DESC;

I've created a MySQL 8.0 fiddle here to help provide better context:
https://dbfiddle.uk/M6HT_R13

NB: I've pared down query #1 to make it easier to work on so some fields in the fiddle can be ignored.

*Edit: Set the parentid variable in the fiddle to see how the current query pulls:

• 34 => product with a sub-bundle component (ZWK180) that isn't found
• 36 => product with single level components that are all found.*

Some additional notes.

The subquery in the query #1 is designed to pull the preferred or (if not set) the lowest supplier cost from the supplier2cost table and it's this subquery that I'm unsure how to implement in a CTE context, if at all.

If additional context would be helpful please ask and I'll edit the query to provide that information.

Expected/Intended Output

ProductSKU	ProductLabel	BundleParentID	Component_ID	Level	Qty	Preferred	SupplyQty	Cost	AdjustedCost
Sub-Comp#1	CMP#2	36	35	2	1	1	1	cost	per_unit_cost
Sub-Comp#2	CMP#3	36	37	2	1	1	1	cost	per_unit_cost
Sub-Comp#3	CMP#4	36	38	2	1	1	1	cost	per_unit_cost
Component#1	CMP#1	34	33	1	1	1	1	cost	per_unit_cost
Sub-Bundle	BUND#1	36	33	1	1	1	1	cost	per_unit_cost

The data would ultimately be used to provide a component cost table like this:

Answer 1

You probably want something like this :

On the second cte, a condition have been added to join your principal query with the recursive query to extract only the selected ones

SET @parentid = 34;
WITH RECURSIVE components AS 
(
    SELECT componentid, p.product_sku, 1 AS level
    FROM component2bundle
    JOIN products p ON componentid = p.product_id
    WHERE bundle_parentid = @parentid
    UNION ALL
        SELECT c2b.componentid, product_sku, c.level+1
        FROM components c, component2bundle c2b
        WHERE c2b.bundle_parentid = c.componentid
),
CTE AS (
    SELECT
    sub.product_sku AS product_sku,
    sub.product_label AS product_label,
    c2p.bundle_parentid AS bundle_parentid,
    c2p.componentid AS comp_product_id,
    sub.qty AS qty,
    sub.preferred AS preferred,
    sub.supply_qty AS supply_qty,
    sub.cost AS cost,
    ROUND((SELECT cost)/(SELECT supply_qty),2) AS adjusted_cost,
    c.level
    FROM products AS p
    JOIN component2bundle AS c2p ON c2p.componentid = p.product_id
    JOIN components c on c.componentid = c2p.componentid
    JOIN (
        /* Get the preferred/cheapest supplier date for this component */
        SELECT
            p2.product_sku AS product_sku,
            p2.product_label AS product_label,
            IFNULL(s2p2.cost, NULL) AS cost,
            s2p2.productid AS product_id,
            s2p2.supplier_preferred AS preferred,
            s2p2.product_quantity AS supply_qty,
            c2p2.componentid AS comp_product_id,
            c2p2.component_quantity AS qty,
            c2p2.bundle_parentid AS bundle_parentid
            FROM products AS p2
                INNER JOIN supplier2product AS s2p2 ON s2p2.productid = p2.product_id
                INNER JOIN component2bundle AS c2p2 ON s2p2.productid = c2p2.componentid
            WHERE c2p2.c2p_archive = 0
                AND COALESCE(s2p2.s2p_archive,0) = 0
            ORDER BY c2p2.componentid ASC, s2p2.supplier_preferred DESC, s2p2.cost ASC
    ) AS sub
    ON (sub.product_id = c2p.componentid)
)
SELECT *
FROM CTE c
WHERE preferred = 1

Demo here