I have a car rental project, when renting an available car from database table CAR with car ID as primary key, I insert data into table RESERVATION with reservation ID as primary key and car ID as foreign key from car table.

However, I don't want the car I just rented out to appear as an "available car" to rent. What query should I write in order to hide it?

Select cars.* 
from cars, reservation 
where ?

2 Answers

5
John Wu On Best Solutions

You should not need to update any tables to hide the car.

When selecting the list of available cars for display, use this SQL:

SELECT * FROM Car WHERE CarID NOT IN (SELECT CarID FROM Reservation)

This will exclude any cars that have already been reserved. That's the basic idea, anyway.

It's likely that reservations are only good for a certain period, and the user may be attempting to reserve a different period, so you might need something more complicated, like:

SELECT * FROM Car WHERE CarID NOT IN
(
    SELECT CarID
    FROM   Reservation
    WHERE  StartDate < @DesiredEndDate
    AND    EndDate   > @DesiredStartDate
)

This will provide a list of cars that do not appear in a reservation that overlaps the user's desired reservation time.

0
Deepshikha On

You can write a join query as:

Select C.carID
from @Car C
Left join @Reservation R on C.carID = R.carID
where R.reservationID is null