Saturday, January 12, 2019

Retrieve all the employees under a manager from a self-referencing table in MySQL

In previous post, we discussed how we can manage to store the hierarchical data in MySQL using self-referencing relationship.
Now, we will see how we can retrieve the data back.
Here, we have manager-employees relationship. One manager has other employees (maybe manager or non-manager) working under him. We will retrieve the whole list of employees under a manager.

SQL Code
SET @id := '2';
SELECT  id, name, managerid, @id
FROM (SELECT * FROM employee ORDER BY managerid, name) E 
WHERE FIND_IN_SET(managerid, @id) > 0
AND @id := CONCAT(@id, ',', id);

Let's assume the managerid to search is "2".
We first need to sort employee table (derived table E) by managerid to make sure that records are in correct order. (This is required as we iterate the record from beginning and add to manager list if conditions matched).
FIND_IN_SET returns the position of a string within a list of strings. (Greater than zero means this employee is under him).
If current record work under this manager(s), CONCAT the id of current record into manager list. (He may have other employees under him).

4 comments:

  1. Amazing post.Thanks for your details and explanations..I want more information from your side.Thank you

    Article submission sites
    Technology

    ReplyDelete
  2. Best casinos in Vegas in 2021, ranked & reviewed
    Find the best slots 창원 출장마사지 online & on 태백 출장마사지 our blacklist of casinos in 2020. It's best to have an idea of 춘천 출장샵 the best casinos you What 전주 출장마사지 are the best online slots?Which slots can I 안동 출장안마 play for free in 2020?

    ReplyDelete

Image compression using C#

Sometimes, we need to compress the image files while maintaining the image quality. This can be achieved by the following C# implementation....