How to intersect on same table dynamically

Question

Say I have a table (city, country). I want to only get cities that are common in all countries.

create table #cities(city nvarchar(10), country nvarchar(10))

insert into #cities(city, country)
select 'NY', 'US' UNION
select 'London', 'UK' UNION
select 'London', 'US'


select city from #cities where country = 'US'
intersect
select city from #cities where country = 'UK'

How can I achieve this dynamically and preferably without cursors if the list of countries is not known upfront.

Answer 1

You can do:

select city
from #cities
group by city
having count(*) = (select count(distinct country) from #cities)

Answer 2

Hello or you can use a self join like this SELECT DISTINCT A.CITY FROM CITIES A JOIN CITIES B ON A.CITY =B.CITY AND A.COUNTRY<>B.COUNTRY;