'SELECT values of a column that are not in another column in the same table MySQL

I have a sample table called "Quant1" as follows (ID - int PK, Name - Varchar(45), Salary - float, manager_id - int):

ID Name Salary Manager_id
1 John 300 3
2 Mike 200 3
3 Sally 550 4
4 Jane 500 7
5 Joe 600 7
6 Dan 600 3
7 Phil 550 NULL

I want to list employees not managing anyone, i.e. ID 1,2,5,6.So I write the following code:

SELECT *
FROM quant1
WHERE id NOT IN 
    (SELECT DISTINCT manager_id
    FROM quant1);

But it doesn't result in the desired outcome. Can anyone spot the error in my script? Thanks in advance!

mysql


Solution 1:[1]

This query will return all rows from the table_name table where the column_name column is not contained in the column_name column from the table_name table.

SELECT * FROM table_name WHERE column_name NOT IN (SELECT column_name FROM table_name)

Solution 2:[2]

SELECT *
FROM quant1
WHERE id NOT IN 
    (SELECT DISTINCT manager_id
    FROM quant1 WHERE manager_id IS NOT NULL);

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 sharma ji
Solution 2 Aakash Yadav

Related Questions