SQL query to list cities in which employee did not work from below "employee" table:

Example: if I give "srini", query should return "Atlanta" and "Redmond"

I tried below 2 queries with no luck, it returns empty results:

1.  SELECT t1.city 
    FROM employee t1 
        JOIN employee t2 ON t1.name=t2.name 
    WHERE t1.city != t2.city 
    WHERE name='srini'
   

2. SELECT city 
    FROM (SELECT city FROM employee WHERE name='srini') as e1 
    WHERE city <> e1.city
   

CodePudding user response：

This should work:

select distinct city
from employee
where city not in
(select city
from employee 
where name = 'srini')

Basically it's selecting all city names that don't exist in a row where name is 'srini'

CodePudding user response：

SQL is such fun. I'd go with a GROUP BY query, where I use the HAVING clause to only return cities where no srini lives.

select city
from employee
group by city
having sum(case when name = 'srini' then 1 else 0 end) = 0

Core ISO/ANSI SQL, i.e. every dbms is expected to support it.

Or use EXCEPT:

select city from employee
EXCEPT
select city from employee where name = 'srini'

Core ISO/ANSI SQL, i.e. every dbms is expected to support it.