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.
>Solution :
You can do:
select city
from #cities
group by city
having count(*) = (select count(distinct country) from #cities)