SQL query to list cities in which employee did not work

user_12919060 0
Viewed 160

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

name city
srini seattle
ross atlanta
rich redmond

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
sql
2 Answers

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'

user_176450990

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.

user_37060160
Related