Querying all unique city names in one state and no where else

January 19, 2023

I am trying to query all unique city names for one particular state without pulling other states. This is what I am trying to do:

SELECT DISTINCT city_name
FROM table
WHERE state = 'California';

This does not work because there is a San Diego in other states aside from California. How would I query this so that it only gives me city_names unique only to California and not other US states? Can someone please help? Thank you for your time.

SELECT DISTINCT city_name
FROM table
WHERE state = 'California';

The desired output is unique city names only in California and no where else.

>Solution :

This query is using a subquery in the WHERE clause to filter the results from the table "mytable" (aliased as "t1"), by checking that the city_name does not exist in the table with a different state, the subquery uses a NOT EXISTS clause to check that the city_name does not exist in the table "mytable" (aliased as "t2") with a different state than ‘California’.

select distinct t1.city_name from mytable t1
where state = 'California'
and not exists (select 1 from mytable t2 
                where state != 'California'
                and t2.city_name = t1.city_name)