CODEWITHSUNDEEP
mysqljoin
gmoore
gmoore

Reputation: 5566

mysql - Finding count of 0 when using joined tables

EDIT I've put up an sqlfiddle with this schema here: http://sqlfiddle.com/#!2/0726f2. I'm trying to select customers 3, 4, 5, 6.

Consider a db with three tables:

customers
---------
id

seats
-----
id
buyer_id (fk to customers)
flight_id

flights
-------
id
datetime (This is the UTC time of the flight)

I'm trying to find customers who have not booked seats on any flight in March.

This query provides a list of customers who have not booked seats on any flight:

SELECT customers.id, count(seats.id) as seat_count FROM `customers` 
LEFT JOIN `seats` ON `seats`.`buyer_id` = `customers`.`id` 
LEFT JOIN `flights` ON `flights`.`id` = `seats`.`flight_id` 
GROUP BY customers.id
HAVING seat_count=0

I tried this query to find a list of customers who have not booked seats on any flight in March

SELECT customers.id, count(seats.id) as seat_count FROM `customers` 
LEFT JOIN `seats` ON `seats`.`buyer_id` = `customers`.`id` 
LEFT JOIN `flights` ON `flights`.`id` = `seats`.`flight_id` 
WHERE flights.datetime >= '2014-03-01 00:00:00'
AND flights.datetime <=   '2014-04-01 00:00:00'
GROUP BY customers.id
HAVING seat_count=0

But it returns an empty list. I understand why: I'm selecting a list of customers who have booked seats in March then finding customers in that list who have not booked seats. Clearly an empty set.

Likewise with adding this to the WHERE clause

AND seats.is is null

I can't figure a proper way to do this.

I've tried:

Because this is wrapped up in a larger search feature, I can't come at this from another direction (selecting from seats and going from there, for example). Schema changes are not possible.

Thanks.

Upvotes: 0

Views: 63

Answers (2)

CodeBird
CodeBird

Reputation: 3858

this works:

SELECT customers.id, count(seats.id) as seat_count FROM `seats` 
INNER JOIN (SELECT id FROM flights WHERE DATE(flights.datetime) >= '2014-03-01'
AND DATE(flights.datetime) <='2014-04-01') `flights` ON `flights`.`id` = `seats`.`flight_id` 
RIGHT JOIN customers ON customers.id=seats.buyer_id
GROUP BY customers.id
HAVING seat_count=0

here's the fiddle

here's another way to do it:

SELECT customers.id FROM customers WHERE id NOT IN (SELECT seats.buyer_id FROM seats
INNER JOIN `flights` ON `flights`.`id` = `seats`.`flight_id` 
WHERE flights.datetime >= '2014-03-01 00:00:00'
AND flights.datetime <=   '2014-04-01 00:00:00')

second fiddle

Upvotes: 1

Ulrich Thomas Gabor
Ulrich Thomas Gabor

Reputation: 6654

You can use NOT EXISTS like

SELECT *
FROM customers
WHERE NOT EXISTS (
  SELECT * FROM seats 
  INNER JOIN flights ON flights.id = seats.flight_id
  WHERE flights.datetime >= '2014-03-01 00:00:00'
  AND flights.datetime <=   '2014-04-01 00:00:00'
  AND seats.buyer_id = customers.id
)

here is a corresponding SQLFiddle.

By the way you should at least add an index on seats.buyer_id, since this is a column you need to join on. With the named index the execution plan does not look that bad.

Upvotes: 1

Related Questions