Get all the users that are managed by a targeted manager

Question

I have the following model:

Table user:  (id, name, direct_manager_id) 

Table customer: (id, name, managed_by_id)

i.e. users can have another user as their direct manager, Customers can be managed by a user.

My requirement is to create a simple select query that will retrieve all the customers that are managed by a specific user. moreover I would like that query to retrieve all the customers that are managed by users that their direct manager is that same user (1 level deep of direct management). and to retrieve all the customers that their direct manager is manged by a user that the direct manager of him is the targeted user (2nd level deep of direct management).... (to the nth level deep)

Here is a picture of the hierarchy:

           targeted User
            /          \ 
           user1         user2
           /  \              / \
          c1   c2           c3  user3
                                    /
                                   c4

So the select query with the targeted user id will select the customers c1-c4

Answer 1

No simple select exists because MySQL does not support Recursive CTE queries like SQL Server.

This being the case, I wrote up some Stored Procedures to run iterative techniques for gathering children of a parent out of a hierarchical data design.

Here is my old post from Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs

I have three Stored Procedures

• GetParentIDByID
• GetAncestry
• GetFamilyTree

In your particular case, you need to do two things

• Change GetFamilyTree to gather all users from the user table under the target user
• LEFT JOIN the result from GetFamilyTree with the customer table (I suggest LEFT JOIN rather than INNER JOIN in the event a user does not have customers, has not been fired, and it still in this hierarchy).

Answer 2

What you are looking for is known as the transitive closure of your parents relation. A. Aho and J. Ullman states back in 1979 that ”There is no relational algebra (or relational calculus) expression that defines the Transitive Closure of a given - binary - relation”.

In SQL99 Recursive Common Table Expression's where introduced which extends the computational power of SQL. These days most DBMS's (DB2, Oracle, Postgres, SQL server to mention a few) implements RCTE's one way or another, but MySQL does not. That means you have to ask one question, get the answer, ask next question and so forth. It is possible to stuff this in a procedure like RolandoMySQLDBA suggest.

Another possibility is to change the model one way or another. Nested Sets have been widely popularized by Joe Celko in his SQL for smarties series. There is an article by Mike Hillyer that disusses this: http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/hierarchical-data.html

Another common implementation is known as Materialized Path. In all essential you add an attribute that represent the path from root to the current node. It pretty much corresponds to the way we think of a file in a unix filesystem. The following article by Vadim Tropashko compares Materialized Path with Nested Sets: https://communities.bmc.com/docs/DOC-9902

Finally you can add another table that represents the transitive closure. I've written a few notes on the topic at: http://dustbite.se/tree/