Recursive select Mysql

Question

I have one table with example data:

+----+---------+
| id | rede_id |
+----+---------+
|  1 |       0 |
|  2 |      38 |
|  3 |       1 |
| 38 |       1 |
| 40 |       1 |
| 41 |      38 |
| 42 |      38 |
| 43 |      40 |

rede_id means what id some person belongs to. Its a network system.

For example, if I need to check network of id=1, the results needs to be like:

+----+---------+
| id | rede_id |
+----+---------+
|  3 |       1 |
| 38 |       1 |
| 40 |       1 |
| 41 |      38 |
| 42 |      38 |
| 40 |       1 |

And if rede_id of someone is '41' or '42' needs to be on results to. Goes to infinite. I can have N rede_id with my id, N rede_id with some id that belongs to me and infinite... I need to get all results.

I don't know how to do that... Sincerely no I ideia.

Answer 1

MySQL 8.0 now supports recursive queries, documented here: https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive

Before MySQL 8.0, there's no easy solution for querying this type of data.

There are alternative ways of storing the data, to make it easier to query.

See also:

• What is the most efficient/elegant way to parse a flat table into a tree?
• https://www.slideshare.net/billkarwin/models-for-hierarchical-data