'In' operator in mySQL

May 6, 2024

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?

>Solution :

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
);