Using NOT IN and count with query
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 tab开发者_Python百科le 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 :-(
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.
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)
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
精彩评论