sql query to select matching rows for all or nothing criteria

Question

I have a table of cars where each car belongs to a company. In another table I have a list of company locations by city.

I want to select all cars from the cars table whose company has locations on all cities passed into the stored procedure, otherwise exclude those cars all together even if it falls short of one city.

So, I've tried something like:

select id, cartype from cars where companyid in 
(
  select id from locations where cityid in 
  (
     select id from cities
  )
)

This doesn't work as it obviously satisfies the condition if ANY of the cities are in the list, not all of them.

It sounds like a group by count, but can't make it work with what I tried.

I"m using MS SQL 2005

It is called relational division. See for example: "http://stackoverflow.com/questions/23707292/find-all-parent-records-where-all-child-records-have-a-given-value-but-not-just/23707565#23707565" — Lennart - Slava Ukraini, May 21 '14 at 04:55

score 2 · Accepted Answer · answered May 21 '14 at 05:15

2

One example:

select id, cartype from cars c
where ( select count(1) from cities where id in (...))
    = ( select count(distinct cityid)
        from locations 
        where c.companyid = locations.id and cityid in (...) )

answered May 21 '14 at 05:15

Lennart - Slava Ukraini

6,936
1
20
32

score 0 · Answer 2 · answered May 21 '14 at 05:08

Maybe try counting all the cities, and then select the car if the company has the same number of distinct location cities are there are total cities.

SELECT id, cartype FROM cars
WHERE
    --Subquery to find the number of locations belonging to car's company
    (SELECT count(distinct cities.id) FROM cities
    INNER JOIN locations on locations.cityid = cities.id
    WHERE locations.companyId = cars.companyId)
    =
    --Subquery to find the total number of locations
    (SELECT count(distinct cities.id) FROM cities)

I haven't tested this, and it may not be the most efficient query, but I think this might work.

score 0 · Answer 3 · answered May 21 '14 at 05:09

0

Try this

SELECT e.* 
FROM cars e
WHERE NOT EXISTS (
    SELECT 1 
    FROM Cities p
    WHERE p.location = e.Location
)

answered May 21 '14 at 05:09

Jaynesh Shah

158
1
1
14

sql query to select matching rows for all or nothing criteria

3 Answers3