[mysql] How to create a MySQL hierarchical recursive query?

You can do it like this in other databases quite easily with a recursive query (YMMV on performance).

The other way to do it is to store two extra bits of data, a left and right value. The left and right value are derived from a pre-order traversal of the tree structure you're representing.

This is know as Modified Preorder Tree Traversal and lets you run a simple query to get all parent values at once. It also goes by the name "nested set".

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to hierarchical-data

How to create a MySQL hierarchical recursive query? What are the options for storing hierarchical data in a relational database? SQL recursive query on self referencing table (Oracle) Simplest way to do a recursive self-join? What is the most efficient/elegant way to parse a flat table into a tree?

Examples related to recursive-query

Error - "UNION operator must have an equal number of expressions" when using CTE for recursive selection How to create a MySQL hierarchical recursive query? How to do the Recursive SELECT query in MySQL?