Using NOT IN and count with query

Question

Having problems with A count of the number of customers who have not hired cars

create table customer
(customer_id  char(4) primary key not null,
customer_sname varchar (30) not null,
customer_fname varchar (30) not null,
customer_title varchar (6) not null,
customer_address1 varchar (35) not null,
customer_address2 varchar (35) null,
customer_postcode varchar (25) null,
customer_phone varchar (30) null,
customer_email varchar (40) null,
customer_di varchar (40) not null)
ENGINE=InnoDB;

create table car_booking
(booking_id INTEGER AUTO_INCREMENT primary key not null,
car_id char (4) not null,
customer_id char (4)  not null,
hire_sdate date not null,
hire_edate date not null)
engine=innodb;

This doesn't seem to work

SELECT customer_id count(*) 
FROM customer
WHERE customer_id not IN
(SELECT booking_id FROM car_booking

help me :-(

Answer 1

You're missing a comma and GROUP BY clause; it should be:

SELECT customer_id, count(*) 
FROM customer
WHERE customer_id not IN
(SELECT booking_id FROM car_booking)
GROUP BY customer_id

Answer 2

Two errors:

• Your select statement shouldn't include both the customer number and the count.
• Your subselect was selecting the booking id instead of the customer id.

Try this:

SELECT count(*) 
FROM customer
WHERE customer_id NOT IN
(SELECT customer_id FROM car_booking)

Answer 3

This should also work. IMO it's better than a subquery.

SELECT COUNT(*)
FROM customer
LEFT JOIN car_booking
ON customer.customer_id = car_booking.customer_id
WHERE car_booking.customer_id IS NULL

Edit: The explanation.

The LEFT JOIN will bring in all records from the table customer, even those that don't have a record in car_booking. Meanwhile, in the joined result set, car_booking.customer_id will be NULL for all entries in customer that don't have an entry in car_booking. The WHERE clause specifies just these rows, which COUNT(*) counts.