subquery with NOT IN

Question

I have a table of cities like:

state  city
-----  ----
texas  houston
texas  austin
texas  dallas
texas  san antonio
texas  beaumont
texas  brownsville
texas  el paso
california  anaheim
california  san diego
california  los angeles
california  oakland
california  simi valley
california  san francisco

I need a query to find the states that don't have a city named 'houston' or 'dallas'. My first thought was this

select distinct state from cities where city not in ('houston', 'dallas');

but that won't work. I think I need a subquery and a NOT IN of some sort..

Answer 1

A way you can do this is with a NOT EXISTS clause:

Select  Distinct State
From    Cities  C1
Where   Not Exists
(
    Select  *
    From    Cities  C2
    Where   C2.City In ('Houston', 'Dallas')
    And     C1.State = C2.State
)

Answer 2

select distinct state from cities where state not in (SELECT state FROM cities WHERE city in ('houston', 'dallas'));