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