It's a hierarchy. The main method used for hierarchies in SQL is the edge list, often misnamed in the SQL world as the "adjacency list", but
adjacency lists are not edge lists.
Trouble is, the logic for traversing edge lists is recursive. Before MariaDB 10.2.2 and MySQL 8, MySQL doesn't do recursion, so you need to do one of two things ...
- implement the recursive logic in stored procedures, or
- use Celko's "nested set" method of storing and retrieving hierarchies.
Both methods are laid out with examples at
https://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html
SQL Server, PostgreSQL and Oracle have implemented recursive logic in a SQL enhancement known as
Common Table Expressions (CTEs). Starting with version 10.2.2, MariaDB has CTEs. MySQL 8.0 has them in a hard-to-use "development" build, and it appears they will be available in a more generally usable build fairly soon.
So if you're using MariaDB 10.2.2, CTEs are available to you now. They're also covered in the ref cited.
Edited 2 time(s). Last edit at 11/12/2016 01:35PM by Peter Brawley.