MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.30


DROP TABLE IF EXISTS `component2bundle`;
CREATE TABLE `component2bundle` (
  `bundle_parentid` mediumint UNSIGNED NOT NULL COMMENT 'productid for our parent/bundle product',
  `componentid` mediumint UNSIGNED NOT NULL COMMENT 'productid that is a component of a bundle',
  `component_quantity` smallint UNSIGNED NOT NULL COMMENT 'how many of the component in this bundle',
  `c2p_archive` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Component inactive/archived'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `product_id` mediumint UNSIGNED NOT NULL,
  `product_sku` varchar(20) NOT NULL,
  `product_label` text NOT NULL,
  `product_desc` text NOT NULL,
  `product_cat` varchar(20) NOT NULL,
  `product_type_id` mediumint UNSIGNED NOT NULL,
  `product_qty` mediumint UNSIGNED NOT NULL,
  `product_type_units` tinyint UNSIGNED NOT NULL,
  `product_weight` smallint UNSIGNED NOT NULL,
  `product_barcode` decimal(48,0) UNSIGNED NOT NULL,
  `product_component` tinyint(1) NOT NULL COMMENT 'Can this be selected as a component in other products (bundles)',
  `product_notes` text NOT NULL,
  `product_cost` double(8,2) UNSIGNED NOT NULL,
  `product_customsid` smallint UNSIGNED NOT NULL,
  `product_bundle` tinyint(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `supplier2product`;
CREATE TABLE `supplier2product` (
  `s2p_id` int UNSIGNED NOT NULL,
  `productid` mediumint UNSIGNED NOT NULL,
  `supplierid` smallint NOT NULL,
  `supplier_sku` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `supplier_preferred` tinyint(1) DEFAULT NULL,
  `product_quantity` smallint UNSIGNED NOT NULL COMMENT 'Quantity of product for this SKU',
  `supplier_unit_typeid` tinyint UNSIGNED NOT NULL,
  `cost` double(8,2) UNSIGNED NOT NULL,
  `currencyiso3` varchar(3) NOT NULL,
  `leadtime` smallint UNSIGNED NOT NULL COMMENT 'leadtime in days',
  `s2p_archive` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ALTER TABLE `component2bundle`
  ADD PRIMARY KEY (`bundle_parentid`,`componentid`);

ALTER TABLE `products`
  ADD PRIMARY KEY (`product_id`),
  ADD UNIQUE KEY `product_sku` (`product_sku`);

ALTER TABLE `supplier2product`
  ADD PRIMARY KEY (`s2p_id`),
  ADD UNIQUE KEY `productid` (`productid`,`supplierid`,`supplier_sku`);

Records: 0  Duplicates: 0  Warnings: 0



Records: 0  Duplicates: 0  Warnings: 0



Records: 0  Duplicates: 0  Warnings: 0


INSERT INTO `component2bundle` (`bundle_parentid`, `componentid`, `component_quantity`, `c2p_archive`) VALUES
(34, 33, 1, 0),
(34, 36, 1, 0),
(36, 38, 2, 0),
(36, 37, 10, 0),
(36, 35, 1, 0);

INSERT INTO `products` (`product_id`, `product_sku`, `product_label`, `product_desc`, `product_cat`, `product_type_id`, `product_qty`, `product_type_units`, `product_weight`, `product_barcode`, `product_component`, `product_notes`, `product_cost`, `product_customsid`, `product_bundle`) VALUES
(33, 'WICRST-1', 'WICReset Key x1', 'WICReset key code [single]', 'waste_kit', 1, 1, 0, 145, 5060273100482, 1, 'Digital product', 0.00, 1, 0),
(34, 'WK1XP2', 'Printer Potty: Solo XP2', 'Printer Potty Waste kit (no key)', 'waste_kit', 1, 1, 0, 95, 5060273100680, 0, '', 0.00, 1, 0),
(35, 'IMG797-125', 'STS Yellow - 125ml', '125ml of STS WJ0797 yellow ink for Canon', 'ink', 2, 1, 4, 140, 0, 1, '', 0.00, 15, 0),
(36, 'ZWK180', 'Printer Potty 180ml Tank', 'Tank unit for Printer Potty (180ml)', 'waste_kit', 1, 1, 0, 85, 0, 1, '', 0.00, 1, 1),
(37, 'ZNUTSTL', 'Steel Nut', 'Nut for waste ink kits', 'parts', 1, 1, 0, 5, 0, 1, '', 0.00, 1, 0),
(38, 'ZCRG', 'Code Ring (Green)', 'Code ring part - Green', 'parts', 1, 1, 0, 1, 0, 1, '', 0.00, 1, 0);

INSERT INTO `supplier2product` (`s2p_id`, `productid`, `supplierid`, `supplier_sku`, `supplier_preferred`, `product_quantity`, `supplier_unit_typeid`, `cost`, `currencyiso3`, `leadtime`, `s2p_archive`) VALUES
(1, 33, 6, 'WICKey-500', 1, 500, 0, 1490.00, 'usd', 1, 0),
(2, 33, 6, 'WICKey-1', 0, 1, 0, 9.95, 'usd', 1, 0),
(4, 33, 6, 'WICKey-100', 0, 100, 0, 395.00, 'usd', 1, 0),
(5, 33, 6, 'WICKey-50', 0, 50, 0, 249.00, 'usd', 1, 0),
(11, 35, 4, 'WJ0797-1L', 0, 1, 7, 75.00, 'usd', 30, 0),
(12, 35, 4, 'WJ0797-5G', 0, 1, 9, 124.95, 'usd', 30, 0),
(13, 35, 4, 'WJ0797-1G', 1, 1, 8, 100.00, 'usd', 30, 0),
(15, 37, 7, 'ZNUTSTL', 1, 1000, 0, 250.00, 'gbp', 7, 0),
(16, 38, 7, 'ZCRG', 1, 500, 0, 175.00, 'gbp', 14, 0);

Records: 5  Duplicates: 0  Warnings: 0



Records: 6  Duplicates: 0  Warnings: 0



Records: 9  Duplicates: 0  Warnings: 0


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
)
SELECT *
FROM components;


componentid
product_sku
level


33
WICRST-1
1

36
ZWK180
1

35
ZWK180
2

37
ZWK180
2

38
ZWK180
2


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


product_sku
product_label
bundle_parentid
comp_product_id
qty
preferred
supply_qty
cost
adjusted_cost
level


WICRST-1
WICReset Key x1
34
33
1
1
500
1490.00
2.98
1

IMG797-125
STS Yellow - 125ml
36
35
1
1
1
100.00
100
2

ZNUTSTL
Steel Nut
36
37
10
1
1000
250.00
0.25
2

ZCRG
Code Ring (Green)
36
38
2
1
500
175.00
0.35
2
product_sku	product_label	bundle_parentid	comp_product_id	qty	preferred	supply_qty	cost	adjusted_cost	level
WICRST-1	WICReset Key x1	34	33	1	1	500	1490.00	2.98	1
IMG797-125	STS Yellow - 125ml	36	35	1	1	1	100.00	100	2
ZNUTSTL	Steel Nut	36	37	10	1	1000	250.00	0.25	2
ZCRG	Code Ring (Green)	36	38	2	1	500	175.00	0.35	2