CODEWITHSUNDEEP
sqlpostgresql
Niels Kristian
Niels Kristian

Reputation: 8845

SQL: count all where has no relation with certain condition

I have a table called cars. Cars has a one-to-many relation to tires table. Tires has a column called condition which can either be "flat" or "pumped".

I want to do a query where I count how many cars that has no pumped tires left. Meaning that ALL tires has to be flat. In order for the car to be counted. How do I do that

I was trying something like:

SELECT COUNT(*) 
FROM "cars" 
  left join tires on cars.id = tires.car_id 
WHERE "tires"."condition" = 'flat' 
AND "tires"."id" IS NULL

But that does not seem to be exactly right...

Here is a data sample. The result should of cause be a count of 1. Only car with id 3 has no pumped tires

# Cars
id        brand         model
1         Audi          A4
2         BMW           X5
3         Ford          Mondeo

# Tires
id        car_id        condition
1         1             flat
2         1             pumped
3         1             pumped
4         1             flat
5         2             pumped
6         2             pumped
7         2             pumped
8         2             pumped
9         3             flat
10        3             flat
11        3             flat
12        3             flat

EDIT condition and car_id is indexed and there are millions of rows - so it needs to be performant

Upvotes: 2

Views: 580

Answers (3)

David Aldridge
David Aldridge

Reputation: 52386

Ordinarily I would just:

SELECT COUNT(*) 
FROM   cars
where  not exists (select null
                     from tires
                    where tires.car_id    =  cars.id and
                          tires.condition = 'pumped')

If, however, you had an extremely small proportion of cars for which any tire at all was 'flat' then I would consider filtering the cars down first to a candidate list for which to check for no pumped tires.

SELECT COUNT(*) 
FROM   cars
where  id in (select distinct id
                from tires
               where tires.condition = 'flat') and
        not exists (select null
                     from tires
                    where tires.car_id    =  cars.id and
                          tires.condition = 'pumped')

You'd want indexes on (condition, car_id) and (car_id, condition) for the latter.

Another method which might be of interest is:

select count(*)
from   (
  select   0
  from     tires
  group by car_id
  having   max(condition) = 'flat')

Upvotes: 1

Joachim Weiß
Joachim Weiß

Reputation: 407

Quick and dirty and not tested

select count(*) 
from cars,tires
where cars.id = tires.carid
and tires.status='flat'
and car.id not in (select distinct carid from tires where status="pumped")

Upvotes: 0

jarlh
jarlh

Reputation: 44786

Count all cars that don't have any pumped tires: 

select count(*)
from cars c
where not exists (select 1 from tires t
                  where c.id = t.car_id
                    and t.condition = 'pumped')

Upvotes: 1

Related Questions