'SQL recursive query with multiple references to same table

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.

mysqlsqlcommon-table-expressionhierarchical-datarecursive-query


Solution 1:[1]

Why not use part_of table twice ? join the part-of table itself self join and then query for the id ? it really depends on what level of recursion you are looking for i guess.

Solution 2:[2]

I writing this answer assuming that you will need the packages tables to get the ID, which will then be used to get the parent_id from the part_of table. This part was a bit unclear in your question. If I am wrong in assuming this, then @GBM answer will work as well, as well as mine.

WITH PACKAGES_CTE AS (
    SELECT PACKAGES.ID, PACKAGES.NAME
    FROM PACKAGES
), PACKAGES2_CTE AS
    (
        SELECT *
        FROM PACKAGES2
    )

SELECT P2.PARENT_ID
FROM PACKAGES2_CTE P2
LEFT JOIN PACKAGES2_CTE P3 ON P3.PARENT_ID = P2.ID
WHERE P3.ID = 8

UNION ALL

SELECT P2.PARENT_ID
FROM PACKAGES_CTE P
LEFT JOIN PACKAGES2_CTE P2 ON P.ID = P2.ID
WHERE P2.ID = P2.PARENT_ID OR P.ID = 8

If you have any questions feel free to ask. I did test this using the sample data you provided. Since I already knew the ID, I did not need to use the Packages.Name. I assume you will want to use Packages.Name = 'Package 8' instead of P.ID = 8.

This was an interesting problem!

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 user3342678
Solution 2 MhQ-6

Related Questions