I have 4 database tables Rental Car Company Database, Rental Car Details Database, Rental Car Rates Database, and Booked Car Database.

There are multiple Rental Car companies and locations that have multiple cars with each car having different rates ranges,

What I'm trying to do is query all these databases to find out what locations have only trucks available for dates 03/04/22 - 03/10/22.

I want each location to show how many trucks are available at each location along with the lowest daily rate and the highest daily rate.

The problem is each truck has different prices, so when I do a GROUP BY Location its giving the high and low rate of only one truck when two are available. My low should be the lowest dailyrate of the two and the high rate should be the highest daily rate of the two for the dates i have searched.

The result I get with the data and code below is:

What I'm trying to get is:

I have four database tables like the following

Rental Car Company Database Table Name: (geolocations)

Rental Car Details Database Table Name: (Cars)

Rental Car Rates Database Table Name: (prices)

Booked Car Database Table Name: (bookings)

The code I have tried is below

SELECT Cars.Location, COUNT(Cars.Location) AS CarCount, Cars.Type,
        (SELECT CompanyName
        FROM geolocations
        WHERE geolocations.Location = Cars.Location) AS RentalCarCompanyName,
                        
        (SELECT MIN(dayrate)
        FROM prices
        WHERE Car = Cars.Car AND fromdate <= '2022-03-14' AND DATE_ADD(todate, INTERVAL 1 DAY) > '2022-03-10' LIMIT 1) AS lowrate,
                       
        (SELECT MAX(dayrate)
        FROM prices
        WHERE Car = Cars.Car AND fromdate <= '2022-03-14' AND DATE_ADD(todate, INTERVAL 1 DAY) > '2022-03-10' LIMIT 1) AS highrate                  
FROM Cars
WHERE Car NOT IN (
    SELECT Car
    FROM      bookings
    WHERE      (
        ArrivalDate <= '2022-03-14' AND
        DepartureDate > '2022-03-10'
    ) 
) AND Cars.Type = 'TRUCK'                
GROUP BY Location  
ORDER BY Location ASC

I have a fiddle with data loaded

CodePudding user response：

Here is your updated query

SELECT 
  `cars`.`Location`, 
  COUNT(`cars`.`Location`) AS CarCount, 
  `cars`.`Type`, 
  `geolocations`.`CompanyName`, 
  MIN(`prices`.`dayrate`) AS lowrate, 
  MAX(`prices`.`dayrate`) AS highrate 
FROM 
  `cars` 
  JOIN `geolocations` ON `cars`.`Location` = `geolocations`.`Location` 
  JOIN `prices` ON `cars`.`Car` = `prices`.`Car` 
WHERE 
  `cars`.Car NOT IN (
    SELECT 
      Car 
    FROM 
      bookings 
    WHERE 
      (
        ArrivalDate <= '2022-03-14' 
        AND DepartureDate > '2022-03-10'
      )
  ) 
  AND Cars.Type = 'TRUCK' 
  AND `prices`.`fromdate` <= '2022-03-14' 
  AND DATE_ADD(todate, INTERVAL 1 DAY) > '2022-03-10' 
GROUP BY 
  Location 
ORDER BY 
  Location ASC