Mysql Question posted in
The official MySQL documentation can be found here.

'In' operator in mySQL

Employee:

| id  | name  | department | managerId |
+-----+-------+------------+-----------+
| 101 | John  | A          | null      |
| 102 | Dan   | A          | 101       |
| 103 | James | A          | 101       |
| 104 | Amy   | A          | 101       |
| 105 | Anne  | A          | 101       |
| 106 | Ron   | B          | 101       |
+-----+-------+------------+-----------|

From the above Employee table, we need the names of managers who manages at least 5 employees.
I came up with this query:

select name from employee where managerid in (
    select managerid 
    from employee 
    where managerid is not null 
    group by managerid having count(*) > 4
);

I expected the following output:

| name |
+------+
| John |
+------+

But instead, it throws up:

| ----- |
| Dan   |
| James |
| Amy   |
| Anne  |
| Ron   |

The where clause returns the managerid as 101. But the final select is not picking up the corresponding name. Where is this going wrong?

Tags:

2

Answers


  1. 0

    You are selecting all employees with a manager who has more than 4 employees. You need to change the where clause to filter by id instead of managerid:

    select name from employee where id in (
    select managerid 
    from employee 
    where managerid is not null 
    group by managerid having count(*) > 4
);
    Login or Signup to reply.
  2. 0 
    SELECT DISTINCT e.name 
FROM employee e
JOIN employee m ON e.managerid = m.id
WHERE m.id IN (
    SELECT managerid 
    FROM employee 
    WHERE managerid IS NOT NULL 
    GROUP BY managerid 
    HAVING COUNT(*) > 4
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search