I require a query for the following statement:
Show the details of all staff who have booked more than 4 vehicle rentals for customers.
The schema is as follows:
Staff (staffID, firstname, lastname, xyz..)
Customers (customerID, firstname, lastname, xyz..)
Booking (bookingID, staffID, customerID, vehicleregistration, date)
Vehicle (vehicleregistration, make, model, xyz..)
I gather that all Booking records are actual vehicle rentals for customers. So you want to count these per staff. per staff translates to
GROUP BY staffid. To check aggregation results (the count) you'd use the
select * from staff where staffid in ( select staffid from booking group by staffid having count(*) > 4 );
As you see, there is no need to join anything, as you only want data from the staff table. The booking count is criteria that better belongs in the
Just for completeness sake, here is the same with a join:
select s.* from staff s join ( select staffid from booking group by staffid having count(*) > 4 ) b on b.staffid = s.staffid;
I find this less readable, but some people may prefer it still. You'd use this if you wanted to information from the aggregation in your results, in your case the count.
select s.*, b.bookings from staff s join ( select staffid, count(*) as bookings from booking group by staffid having count(*) > 4 ) b on b.staffid = s.staffid;