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

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.

CodePudding user response：

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)

CodePudding user response：

You can use aggregation and set the conditions in the HAVING clause:

SELECT city_name
FROM tablename
GROUP BY city_name
HAVING COUNT(*) = SUM((state = 'California')::int);

SUM((state = 'California')::int) will be 1 only for city_names that can be found in 'California' and it will be 0 for all other city_names.

COUNT(*) will be 1 only for city_names that exist in only 1 state (and it is never 0).

The only case that these expressions are equal is when they are both 1.