Get data from two tables with recursive relationships I have a MySQL 5.1 DB, being used behind a PHP application to track permissions and other items that need to be issued to staff that hold various positions within an org. To track what privileges a position has, I'm using this table:

mysql&gt; desc position_privs;
+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| position     | int(11) | NO   |     | NULL    |       |
| priv         | int(11) | NO   |     | NULL    |       |
| date_added   | int(11) | NO   |     | 0       |       |
| date_removed | int(11) | YES  |     | NULL    |       |
+--------------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)


position refers to an id column in a positions table, and priv refers to an id column in a privs table. Some positions also inherit privileges from other positions, which may in turn inherit from yet another position, etc. I have this table for that:

mysql&gt; desc position_inherits;
+--------------------+---------+------+-----+---------+-------+
| Field              | Type    | Null | Key | Default | Extra |
+--------------------+---------+------+-----+---------+-------+
| position           | int(11) | NO   |     | NULL    |       |
| inherit_privs_from | int(11) | NO   |     | NULL    |       |
| date_added         | int(11) | NO   |     | 0       |       |
| date_removed       | int(11) | YES  |     | NULL    |       |
+--------------------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)


If the inheritances were always only one level deep, I could do this with a simple JOIN, but I'm stumped as to handling positions that inherit from a position, which in turn inherits from another position.

Is there a convenient way of doing this using MySQL, or should I just stick to SELECT * FROM $table and doing it in the application code instead?
BAD NEWS

MySQL does not support recursive data or table structures. Neither does it support recursive SQL.

GOOD NEWS

There is a hope. I have written some stored functions on how to retrieve hierarchies of data


Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs 
Dec 10, 2012 : MySQL: Tree-Hierarchical query


CAVEAT


You would have to implement this against your tables.
You also need to make sure you are entering data as a Tree and not as a Graph.


https://en.wikipedia.org/wiki/Tree_(data_structure)
http://wiki.answers.com/Q/DIFFERENCE_between_GRAPH_and_TREE_IN_DATA_STRUCTURES
https://stackoverflow.com/a/7707983/491757

For any row in the position_inherits table that does not have a parent, simply set that row's inherit_privs_from to zero(0). That way, you have establish root positions.