CODEWITHSUNDEEP
mysqljoin
online Thomas
online Thomas

Reputation: 9381

How to join on a table where the column should not exist or the queried value should be NOT BETWEEN the dates?

I know my title isn't the best. What I try to accomplish is this:

I have 4 tables:

My query takes 3 arguments:

first we filter parkingspots

INNER JOIN `deb93093_api`.`Addresses`
    ON (
      `deb93093_api`.`Parking_Spots`.`address` = `Addresses`.`uuid`
    )

===

WHERE (`Addresses`.`zipCode` = '4651AA')

Then the available parkingspots

INNER JOIN `deb93093_api`.`Availability`
    ON (
      `Availability`.`parkingSpot` = `Parking_Spots`.`uuid`
    )

===

AND (
     '2016-12-31' BETWEEN `Availability`.`fromDate` AND `Availability`.`toDate`
  )
AND (
    '2016-12-31' BETWEEN `Availability`.`fromDate` AND `Availability`.`toDate`
  )

But here is the problem:

I need to get the parkingspots that are not already booked in bookings for the desired times

-- AND (
 --   '2016-12-31' NOT BETWEEN `Bookings`.`from` AND `Availability`.`to`
 -- )

 -- AND (
 --   '2016-12-31' NOT BETWEEN `Bookings`.`from` AND `Availability`.`to`
 -- )

But an inner join would just try to show the parking spots that have records for them. They should however show the ones without bookings as well. Is there a type of join I can use? I would like to select the avaiable parkingspots, which is

available = parkingspots_with_right_zipcode - only that are available - the ones that are set to available by the owner, but already booked

Full query up to this point:

SELECT
  `Parking_Spots`.*
FROM
  `deb93093_api`.`Parking_Spots`

  INNER JOIN `deb93093_api`.`Addresses`
    ON (
      `deb93093_api`.`Parking_Spots`.`address` = `Addresses`.`uuid`
    )
  INNER JOIN `deb93093_api`.`Availability`
    ON (
      `Availability`.`parkingSpot` = `Parking_Spots`.`uuid`
    )

  OUTER JOIN `deb93093_api`.`Bookings`
   ON (
     `Bookings`.`parkingSpot` = `Parking_Spots`.`uuid`
   )



 WHERE (`Addresses`.`zipCode` = '4651AA')
  AND (
     '2016-12-31' BETWEEN `Availability`.`fromDate` AND `Availability`.`toDate`
  )
  AND (
    '2016-12-31' BETWEEN `Availability`.`fromDate` AND `Availability`.`toDate`
  )

 -- AND (
 --   '2016-12-31' NOT BETWEEN `Bookings`.`from` AND `Availability`.`to`
 -- )

 -- AND (
 --   '2016-12-31' NOT BETWEEN `Bookings`.`from` AND `Availability`.`to`
 -- )

enter image description here

from and to are bookings, so I want to filter our those who are already booked for the desired time. However if there are no records for that parkingspot in bookings, it should still show the parkingspot as it is NOT booked (so available)

PSEUDE CODE TO FURTHER CLARIFY

 (
AND (
WHERE $desired_from NOT BETWEEN Bookings`.`from` AND `Bookings`.`to` )

  AND (
    $desired_to NOT BETWEEN `Bookings`.`from` AND `Bookings`.`to`
  )
OR
WHERE [there is no record in Bookings]

Upvotes: 0

Views: 20

Answers (1)

Cthulhu
Cthulhu

Reputation: 1372

In situation like that you can use LEFT JOIN with additional conditions in the join ON clause and then filter only rows where no joining records where found:

SELECT
  `Parking_Spots`.*
FROM
  `deb93093_api`.`Parking_Spots`

  INNER JOIN `deb93093_api`.`Addresses`
    ON (
      `deb93093_api`.`Parking_Spots`.`address` = `Addresses`.`uuid`
    )
  INNER JOIN `deb93093_api`.`Availability`
    ON (
      `Availability`.`parkingSpot` = `Parking_Spots`.`uuid`
    )

  LEFT JOIN `deb93093_api`.`Bookings`
   ON (
     `Bookings`.`parkingSpot` = `Parking_Spots`.`uuid`
   ) 
   AND (
     '2016-12-31' BETWEEN `Bookings`.`from` AND `Bookings`.`to`
   )

 WHERE (`Addresses`.`zipCode` = '4651AA')
  AND (
     '2016-12-31' BETWEEN `Availability`.`fromDate` AND `Availability`.`toDate`
  )
  AND `Bookings`.`uuid` IS NULL -- THIS MEANS THE SPOT IS NOT BOOKED

Upvotes: 2

Related Questions