SQL statement to select all dead people

I have tables:

• City: zip, name,...
• People: id, city_zip(refers to city.zip), born_time, dead_time

I need to select data about cities where ALL people from that city are dead: born_time NOT NULL AND dead_time < NOW()

because we do not assume that someone is dead if we do not have information.

Answer

You can use not exists:

select c.*
from city c
where not exists (
    select 1 
    from people p1 
    where p1.city_zip = c.zip and (dead_time is null or dead_time > now())
)

This would also return cities that have no people at all. If that’s something you want to avoid, then another option is aggregation:

select c.*
from city c
inner join people p on p.city_zip = c.zip 
group by c.zip
having max(case when dead_time is null or dead_time > now() then 1 else 0 end) = 0

select c.* ... from city c ... group by c.zip is valid standard SQL (assuming that zip is the primary key of table city). However, all databases do not support it, in which case you will need to enumerate the columns you want in both the select and group by clauses.