How to get ID in PostgreSQL?-CodePudding

I am learning SQL using Postgres. Here I want to get the employeeID where the employee does not have any cell phone, type 'C', that is the answer should be 3, 4, 5 because those employees don't have cell phone. I tried the following query but it doesn't give me the right answer instead it gives me 1, 2, 3, 4, 5

SELECT employeeID
FROM PhoneInfo
WHERE Type != 'C';

What am I doing wrong?

CodePudding user response：

SELECT DISTINCT employeeID
FROM PhoneInfo
WHERE employeeID NOT IN (
                         SELECT employeeID
                         FROM PhoneInfo
                         WHERE Type = 'C'
                        );

This query will give you the right answer. Here, we are trying to find the employeeID that does not exist in the list of the employeeIDs that have type C. Hope that helps!

CodePudding user response：

Because there are some rows which are Type <> 'C' from employeeID 1 or 5.

as we can see from your condition we will get some rows from employeeid is 1.

sqlfiddle

If you want to get employeeID which didn't contain any Type = 'C' we can try to use NOT EXISTS subquery

SELECT employeeID
FROM PhoneInfo t1
WHERE NOT EXISTS (
    SELECT 
    FROM PhoneInfo tt
    WHERE t1.employeeID = tt.employeeID
    AND Type = 'C'
)

if you want to get employeeID with no duplicate row we can try to use DISTINCT which might look like your expected.