Hierarchy Query

Question

Im wondering how can I achieve a successful query for a hierarchy structure i have with my users. I had posted a similar question but the structure changed where now only Executives can have costumers assigned to them.

There are this levels of user:

1. Director
2. Manager
3. Executive

Example table USERS:

ID    username    privilege   parent_ID
1     Director1   1           null
2     Director2   1           null
3     Manager1    2           1
4     Manager2    2           1
5     Manager3    2           2
6     Executive1  3           3
7     Executive2  3           3
8     Executive3  3           4
9     Executive4  3           4
10    Executive5  3           5
11    Executive6  3           5

And they will have their "costumers".

Example table COSTUMERS

ID  name  User_ID
1   c1    11
2   c2    10
3   c3    10
4   c4    9
5   c5    8
6   c6    7
7   c7    6

My problem is into what kind of join should i make so that every user will be able to see only the costumers that they are allowed to, which the rule would be that they can only see costumers from the executives below them, and executives will be able to see their own costumers only.

For example in the diagram above, if users was to check his costumers he should see:

Director1: C7,C6,C5,C4

Director2: C3,C2,C1

Manager1: C7,C6

Manager2: C5,C4

Manager3: C3,C2,C1

and the executives only would see their own costumers.

Answer 1

The proper way to solve this is with a recursive CTE query, which are coming in MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/with.html

WITH RECURSIVE h AS (
  SELECT ID FROM USERS WHERE ID = ?
  UNION
  SELECT ID FROM USERS AS u JOIN h ON u.parent_ID = h.ID
)
SELECT c.*
FROM h
JOIN COSTUMERS AS c ON c.User_ID = h.ID;

If you're still using MySQL 5.7 or older, you need to do it more awkwardly. You have one advantage, that your hierarchy has a fixed maximum depth.

SELECT c.*
FROM (
    SELECT e.ID FROM USERS AS e
    WHERE e.ID = ?
    UNION ALL 
    SELECT e.ID FROM USERS AS e 
    JOIN USERS AS m ON e.parent_ID = m.ID
    WHERE m.ID = ?
    UNION ALL 
    SELECT e.ID FROM USERS AS e 
    JOIN USERS AS m ON e.parent_ID = m.ID
    JOIN USERS AS d ON m.parent_ID = d.ID
    WHERE d.ID = ?
) AS h
JOIN COSTUMERS AS c ON c.User_ID = h.ID;

I'm assuming that restructuring your hierarchy into another design like Closure Table isn't a possibility. But if you're interested in other designs, see my answer to What is the most efficient/elegant way to parse a flat table into a tree?

Or my presentation https://www.slideshare.net/billkarwin/models-for-hierarchical-data

Or my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.