SQL recursive query with multiple references to same table

Question

Is there way to solve this problem with some sort of recursive SQL query (MySQL) rather than programmatically doing it.

I have two tables, one contains packages id and name, the other one contains id of package and parent_id which is also reference to id of some other package. What I need to achieve is to get list of all id of packages which contain some specific package.

Below is an example of tables and desired output if I'm looking for package with id 8. Package 8 is part of package 5 and 6, but package 6 is part of package 1 and 4. So, I need all of that ids as array(1,4,5,6).

packages table:

+----+-----------+
| id | name      |
+----+-----------+
| 1  | package 1 |
| 2  | package 2 |
| 3  | package 3 |
| 4  | package 4 |
| 5  | package 5 |
| 6  | package 6 |
| 7  | package 7 |
| 8  | package 8 |
+----+-----------+

part_of table:

+----+-----------+
| id | parent_id |
+----+-----------+
| 6  |     1     |
| 6  |     4     |
| 8  |     5     |
| 8  |     6     |
+----+-----------+

output:

+----+
| id |
+----+
| 1  |
| 4  |
| 5  |
| 6  |
+----+

EDIT: To explain a bit better what this all was about. This packages table is actually car parts table, which also includes price and many more fields. If we look for "timing belt" part, it can be standalone, can be part of some bundle, we call it "timing belt set" or can be part of "full service" at workshop. All of them, "timing belt", "timing belt set", "full service" are stored in same table. Now, when customer comes asking for "timing belt", I can offer him standalone for 50$ or in set for 150$ or I can offer full service for 250$. All of them includes "timing belt" he asked for.

Answer 1

You should be able to achieve this using a MySQL recursive common table expression:

WITH RECURSIVE cte AS (
    SELECT id, parent_id FROM part_of WHERE id = 8
    UNION ALL
    SELECT po.id, po.parent_id FROM part_of po INNER JOIN cte ON cte.parent_id = po.id
)
SELECT parent_id FROM cte

Demo on DB fiddle:

| parent_id |
| --------- |
| 5         |
| 6         |
| 1         |
| 4         |

---

You can JOIN the results with the products table to show the name of each parent product:

WITH RECURSIVE cte AS (
    SELECT id, parent_id FROM part_of WHERE id = 8
    UNION ALL
    SELECT po.id, po.parent_id FROM part_of po INNER JOIN cte ON cte.parent_id = po.id
)
SELECT cte.parent_id, p.name
FROM cte
INNER JOIN packages p on cte.parent_id = p.id

Demo on DB Fiddle:

| parent_id | name      |
| --------- | --------- |
| 1         | package 1 |
| 4         | package 4 |
| 5         | package 5 |
| 6         | package 6 |