Get all the users that are managed by a targeted manager 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
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).