0

Target platform: MySQL 5.7

I have table categories that represents hierarchical categories data:

+----+-----------------+-----------+
| id | name            | parent_id |
+----+-----------------+-----------+
|  1 | First category  |      NULL |
|  2 | Second category |         1 |
|  3 | Third category  |         2 |
|  4 | Other category  |         1 |
+----+-----------------+-----------+

Also, I have another table (categories_relations) that stores relations between category and all other related (or connected) categories:

+----+-----------+-------------+
| id | parent_id | relation_id |
+----+-----------+-------------+
|  1 |         1 |           1 |
|  2 |         1 |           2 |
|  3 |         1 |           3 |
|  4 |         1 |           4 |
|  5 |         2 |           2 |
|  6 |         2 |           3 |
|  7 |         3 |           3 |
|  8 |         4 |           4 |
+----+-----------+-------------+

Is it possible to retrieve filtered categories (for example - by ID) with all related other categories? For example, if I would query category with ID=1, query result should be:

+----+-----------------+-----------+
| id | name            | parent_id |
+----+-----------------+-----------+
|  1 | First category  |      NULL |
|  2 | Second category |         1 |
|  3 | Third category  |         2 |
|  4 | Other category  |         1 |
+----+-----------------+-----------+

If ID=4:

+----+-----------------+-----------+
| id | name            | parent_id |
+----+-----------------+-----------+
|  1 | First category  |      NULL |
|  4 | Other category  |         1 |
+----+-----------------+-----------+

And so on. Thank you.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Lukas Klizas
  • 175
  • 12
  • https://web.archive.org/web/20181221162916/http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ – Barmar Mar 24 '20 at 14:56
  • @Barmar unfortunately, that's not exactly what I need. – Lukas Klizas Mar 24 '20 at 14:58
  • https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive-examples – Bill Karwin Mar 24 '20 at 15:09
  • @BillKarwin as I mentioned in post, target platform is MySQL 5.7, and it does not have recursive expressions. – Lukas Klizas Mar 24 '20 at 15:10
  • I also added the tag [tag:hierarchical-data]. There are numerous questions on Stack Overflow about this subject in MySQL. Before MySQL 8.0, none of the answers are really satisfactory. I posted a highly-upvoted answer: https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree and there's also https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database which has other options. – Bill Karwin Mar 24 '20 at 15:11
  • @LukasKlizas this question is confusing, I'm not sure how your two tables relate to each other. Please explain the relationship better and list the query you have tried so far, even if it doesn't exactly return what you want it to. – Gharbad The Weak Mar 24 '20 at 23:48

0 Answers0