How to UNION product_groups and specifications of different hierachy level to deepest one?

Question

Scenario - All valid classes / product groups and their valid specifications for all products in them are to be determined.

• Products have different specifications / features
• Products always hang on the lowest hierarchy level in the tree of product groups.
• Specifications can be attached to different levels, in which case it can be assumed that they are only attached to the highest (very general) and the lowest level (rather very special dimensions, pressure specifications, ...).

Since the products are linked at the lowest level, it would be simplest, in my view, to combine all the specifications existing at the upper levels in the path at the lowest level.

---

product_groups

id	parent_id
1	NULL
2	1
3	2
4	3
5	NULL
6	5
7	6
8	7

specification_to_group

id	product_group_id	specification_id
1	1	1
2	1	2
3	4	10
4	4	11
5	4	12
6	5	1
7	5	3
8	7	12

result

product_group_id	specification_id
4	1
4	2
4	10
4	11
4	12
8	1
8	3
8	12

---

One approach would be to try to use help tables and be less generic.

SELECT
    lst.id AS lst,
    up1.id AS up1,
    up2.id AS up2,
    up3.id AS up3,
    up4.id AS up4
FROM
    product_groups lst 
        LEFT JOIN
    product_groups up1 ON lst.parent_id = up1.id
        LEFT JOIN
    product_groups up2 ON up1.parent_id = up2.id
        LEFT JOIN
    product_groups up3 ON up2.parent_id = up3.id
        LEFT JOIN
    product_groups up4 ON up3.parent_id = up4.id
WHERE
    lst.id NOT IN
    (SELECT DISTINCT parent_id
        FROM product_groups
        WHERE parent_id IS NOT NULL
)
        
SELECT g.lst, s.specification_id
        FROM ht_tree g
               LEFT JOIN specification_to_group s ON g.lst = s.product_group_id
               UNION SELECT g.lst, s.specification_id
        FROM ht_tree g
               LEFT JOIN specification_to_group s ON g.up1 = s.product_group_id
UNION SELECT g.lst, s.specification_id
        FROM ht_tree g
               LEFT JOIN specification_to_group s ON g.up2 = s.product_group_id
UNION SELECT g.lst, s.specification_id
        FROM ht_tree g
               LEFT JOIN specification_to_group s ON g.up3 = s.product_group_id
UNION 
SELECT g.lst, s.specification_id
        FROM ht_tree g
               LEFT JOIN specification_to_group s ON g.up4 = s.product_group_id;
;

Since there are no permissions for creating tables and also no other option to persist the data already prepared, I would think about a CTE.

Answer 1

This is a classic opportunity to use recursive CTE

WITH recursive  CTE AS(
SELECT `id`, `parent_id`, ROW_NUMBER() OVER (ORDER By id) rn, 1 lvl
FROM  product_groups
WHERE `parent_id` IS NULL
  UNION ALL

  SELECT P1.id, P1.parent_id , M.rn, M.lvl+1 AS LVL
  FROM product_groups P1  
  INNER JOIN CTE M
  ON M.id = P1.parent_id),
  getmax as(
SELECT `id`, MAX(id) OVER(PARTITION BY rn) m FROM CTE
ORDER BY rn)
SELECT sp.id, g.m, sp.`specification_id`
  FROM specification_to_group sp JOIN getmax g ON sp.`product_group_id` = g.id
ORDER By sp.id

id	m	specification_id
1	4	1
2	4	2
3	4	10
4	4	11
5	4	12
6	8	1
7	8	3
8	8	12

fiddle