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,
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,
| 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 |