Home > SQL > Employee Id, Employee Name and Manager Id Self Join SQL Query as Interview Question

Employee Id, Employee Name and Manager Id Self Join SQL Query as Interview Question

One of the most common SQL interview question related to Self Join is that one Employee table with three columns Employee Id, Employee Name and Manager Id.

The interviewer may ask you questions like the following:

  1. Find the employees who are managers.
  2. Find the managers with the count of subordinates
  3. Find the  managers with the count of subordinates greater than 1 (or any other number)

Lets try resolve this  with the basic understanding – what is Self Join?: “A self join is a join in which a table is joined with itself (which is also called Unary relationships), specially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table itself means that each row of the table is combined with itself and with every other row of the table.” – W3Reaource

At first create an Employee table and insert some dummy records into it. Please refer to the  CREATE and INSERT statements below:

Create Employee Table:

CREATE TABLE employees (

emp_id int(11) NOT NULL,
emp_name varchar(45) DEFAULT NULL,
mng_id int(11) DEFAULT NULL,
PRIMARY KEY (emp_id)
)

Insert Dummy Records:

INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (1,'Joe','2');
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (2,'Green',NULL);
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (3,'Jen','2');
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (4,'Brown','1');
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (5,'Harry','1');
INSERT INTO employees (emp_id,emp_name,mng_id) VALUES (6,'Jenny','4');
  1. SQL Statement for “Find the employees who are managers”:
    SELECT DISTINCT e.emp_id AS 'mng_id', e.emp_name AS 'mng_name'
    FROM employees e, employees m WHERE e.emp_id = m.mng_id
    

    In this query you can see the join condition  e.emp_id = m.mng_id where both e and m aliases are of Employees table.

  2. SQL Statement for “Find the managers with the count of subordinates”:
    SELECT COUNT(emp.emp_id) AS 'emp_count', mng.mng_name AS 'mng_name'
    FROM employees emp,
    (SELECT DISTINCT e.emp_id AS 'mng_id', e.emp_name AS 'mng_name'
    FROM employees e, employees m
    WHERE e.emp_id = m.mng_id) mng
    WHERE emp.mng_id = mng.mng_id
    GROUP BY mng.mng_id
    

    In this query we are getting all the managers’ names with their subordinates counts.

  3. SQL Statement for “Find the managers with the count of subordinates greater than 1 (or any other number)”:

    SELECT COUNT(emp.emp_id) AS 'emp_count', mng.mng_name AS 'mng_name'
    FROM employees emp,
    (SELECT DISTINCT e.emp_id AS 'mng_id', e.emp_name AS 'mng_name'
    FROM employees e, employees m
    WHERE e.emp_id = m.mng_id) mng
    WHERE emp.mng_id = mng.mng_id
    GROUP BY mng.mng_id HAVING COUNT(emp.emp_id) > 1;
    

    In this query we need to just add additional Having clause to find out all the managers who have more than specific number of employees.

I hope this post would be helpful for you. Please feel free to leave your comments below in case if you have any suggestion or if you have better optimized solution.

Advertisements
Categories: SQL
  1. sarika
    February 5, 2015 at 3:28 pm

    select manid, count(manid) from emp group by manid having count(manid)>1

  2. June 11, 2016 at 9:40 am

    for 3rd part the below query will also work:

    select t2.EmpID,t2.EmpName–,count(distinct t1.EmpID)
    from sta.hierarchy_test t1
    inner join sta.hierarchy_test t2 on t1.ManagerID = t2.EmpID
    group by t2.EmpID,t2.EmpName
    having count(distinct t1.EmpID) >= 1

  3. Pradeep
    December 5, 2016 at 8:03 pm

    mng.mng_id is invalid identifer… how to solve this problem

  4. Pradeep
    December 5, 2016 at 8:05 pm

    mng.mng_id invalid identifier

  5. Sudhakar. B
    December 22, 2016 at 12:53 pm

    Good job – most useful – thank you !!

  6. January 16, 2017 at 12:45 pm

    In the First Query how we can fetch emp_name at the place of mng_id
    i want to select 1 joe 2 as 1 joe Green
    at the place of 2 there shoule be emp_
    name

  7. Anjan Mcwan
    January 21, 2017 at 4:02 am

    if a manger has Male & Female subordinates. Few managers have only male subordinates, few have only female subordinates. Can you tell me how to select a manager who has both male and female subordinates. where employee table has a column shows the gender of employee

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: