sql - Having count in SELECT clause -

find names of cities hosts both sales , transport departments

for oracle database have table

i.) deptloc

//deptloc city        dname --------------------- newyork       newyork     computer london      science london      sales london      transport 

for sql select statement

select city deptloc      deptloc.dname='sales' or deptloc.dname='transport' group city having count(*)=2; 

the output display

no rows selected. 

my output should be

dname -------- london 

for things this, try use simple join same table. first, have index on table (city, dname).. then

select       d.city          deptloc d          join deptloc d2             on d.city = d2.city            , d2.dname = 'transport'          d.dname = 'sales' 

it may strange, think it. outer portion clause cares cities have 1 of qualifiers. why count cities dont have that. so, join. since know first qualifier on sales covered, re-join dept loc table again, on same city name , second instance of 'transport' component. surprised @ how fast be, on large dataset.