[SOLVED] How to select recursively in mysql?

Issue

I have two tables

components
 id | number | name  | context | ...
-------------------------------------
 1  |  1234  | Test  |   car   | ...
 2  |  1235  | Test  |   car   | ...
 3  |  1236  | Test  |   car   | ...
 4. |  1237  | Test  |   car   | ...
... |   ...  | ...   |   car   | ...
_____________________________________
> 100.000 rows

boms
 id | parent | child | count
----------------------------
 1  |  1234  | 1235  |   1
 2  |  1234  | 1236  |   1
 3  |  1236  | 1237  |   2
... |   ...  | ...   |  ...
____________________________
> 500.000 rows

The purpose of these table is that components is a list of components with all the details related to that component. Table boms is a "Bill of material" that shows, which component is built into another component and how many times.

Result as a tree:

1234
  |-- 1x 1235
  |-- 1x 1236
           |-- 2x 1237

There are many components and many boms, as well as many contexts. How many is unknown and how many levels a BOM can have is unknown, as well.

As I don’t know how to do the following at all, unfortunately, I can’t provide any code snippet:

TLTR:

I want to provide a number and a context and receive a list of all components / children that the provided number in that context has. If context is not provided, the query must give me all children, no matter what the context is.

Here is a fiddle with my example data:
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9599f911adb48563c789fa4fc513195d

Solution

This is a fiddle which builds plain tree representation:

WITH RECURSIVE
cte AS ( 
    SELECT `number`, CAST(`number` AS CHAR(255)) path
    FROM components
    WHERE NOT EXISTS ( SELECT NULL
                       FROM boms
                       WHERE components.`number` = boms.child )
  UNION ALL
    SELECT boms.child, CONCAT(cte.path, '/', boms.child)
    FROM cte
    JOIN boms ON cte.`number` = boms.parent
)
SELECT `number`, components.name, components.context, boms.count, cte.path
FROM cte
JOIN components USING (`number`)
LEFT JOIN boms ON `number` = boms.child

fiddle

If you need the tree for one definite node then modify WHERE in anchor subquery, remove WHERE NOT EXISTS condition and add the condition which selects needed starting node, like WHERE `number` = 1234.

PS. CHAR(255) can be too short – expand if needed.

Answered By – Akina

Answer Checked By – Timothy Miller (BugsFixing Admin)