CODEWITHSUNDEEP
phpmysqlsqldatabase
Ivan
Ivan

Reputation: 5238

List all available cars for a given date SQL

Hello guys am trying to show list of all available cars for a given date i try with BETWEEN but unsuccessfully. Is it a good idea to add another field(status) in the car table and query with status. Has there's a good approach? Or is it better to be displayed by using the booking_date between return_date?

booking:

    id  customer_id  car_id  booking_date  return_date  time_stamp      
------  -----------  ------  ------------  -----------  ----------------
     1            2       5  2015-11-04    2015-11-12   15.06.10 20:10  
     2            3       2  2015-11-02    2015-11-13   15.06.10 20:10  
     3            1      11  2015-11-05    2015-11-16   15.06.10 20:10  
     4            4       8  2015-11-10    2015-12-16   15.06.10 20:10  
     5            0       0  0000-00-00    0000-00-00   15.06.10 20:10

CarType:

   id  car_type_id  branch_id  registration_number  color    date_of_manifacturing  base_price_per_day  
------  -----------  ---------  -------------------  -------  ---------------------  --------------------
     1            1          1  BG-123-431           Bela     2010-11-15                               30
     2            2          1  BG-A32-212           Metalik  2015-11-04                               30
     3            3          2  PA-332-421           Metalik  2008-05-03                               35
     4            4          1  SM-312-542           Crna     2014-06-01                               35
     5            5          1  BG-4393-54           Crna     2012-05-03                               50
     6            6          1  NS-423-64            Bela     2010-10-08                               50

Car:

   id  car_type_id  branch_id  registration_number  color    date_of_manifacturing  base_price_per_day  
------  -----------  ---------  -------------------  -------  ---------------------  --------------------
     1            1          1  BG-123-431           Bela     2010-11-15                               30
     2            2          1  BG-A32-212           Metalik  2015-11-04                               30
     3            3          2  PA-332-421           Metalik  2008-05-03                               35
     4            4          1  SM-312-542           Crna     2014-06-01                               35
     5            5          1  BG-4393-54           Crna     2012-05-03                               50
     6            6          1  NS-423-64            Bela     2010-10-08                               50

My all bookings am listing like this:

SELECT BOOKING.BOOKING_NUMBER, BOOKING.BOOKING_DATE,
BOOKING.RETURN_DATE, AGENCY.NAME, CAR.REGISTRATION_NUMBER,
CAR_TYPE.NAME
FROM BOOKING
JOIN AGENCY ON BOOKING.AGENCY_ID = AGENCY.ID
JOIN CAR ON CAR.ID = BOOKING.CAR_ID
JOIN CAR_TYPE ON CAR_TYPE.ID = CAR.CAR_TYPE_ID

But for all available vehicles I have no idea. Any example?

Upvotes: 1

Views: 2022

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Using your booking table you can know what cars are book on a single day @checkDate

SELECT *
FROM BOOKING
WHERE @checkDate BETWEEN booking_date  AND return_date

So to get available cars on that date, you use:

SELECT *
FROM Cars 
WHERE car_id not in (
                      SELECT car_id
                      FROM BOOKING
                      WHERE @checkDate BETWEEN booking_date  AND return_date  
                     )

OR in your full query

SELECT 
      BOOKING.BOOKING_NUMBER, BOOKING.BOOKING_DATE,
      BOOKING.RETURN_DATE, AGENCY.NAME, 
      CAR.REGISTRATION_NUMBER, CAR_TYPE.NAME
FROM BOOKING
JOIN AGENCY ON BOOKING.AGENCY_ID = AGENCY.ID
JOIN CAR ON CAR.ID = BOOKING.CAR_ID
JOIN CAR_TYPE ON CAR_TYPE.ID = CAR.CAR_TYPE_ID
WHERE
    CAR.car_id not in (
                        SELECT car_id
                        FROM BOOKING
                        WHERE @checkDate BETWEEN booking_date  AND return_date  
                      )

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

You can use LEFT JOIN:

SELECT 
  CAR.REGISTRATION_NUMBER,
  CAR_TYPE.NAME,
  AGENCY.NAME, 
  CASE 
    WHEN BOOKING.CAR_ID IS NULL THEN 'Not Booked' 
    ELSE 'Booked' 
  END AS Status,
  BOOKING.BOOKING_NUMBER, 
  BOOKING.BOOKING_DATE,
  BOOKING.RETURN_DATE
FROM CAR
INNER JOIN CAR_TYPE ON CAR_TYPE.ID      = CAR.CAR_TYPE_ID
LEFT JOIN BOOKING  ON CAR.ID            = BOOKING.CAR_ID
LEFT JOIN AGENCY   ON BOOKING.AGENCY_ID = AGENCY.ID;

This will give you all the available cars, and if any cars has no booking it will come with NULL values for booking and agency, and the Status column will show whether it is booking or not.

Upvotes: 1

Related Questions