displaying available time by comparing 2 tables with a POST variable

these are my tables

RESERVATIONS
+--------+--------------------+-----------------+
|   id   |  reservation_date  | reservation_time|
+--------+--------------------+-----------------+
|   1    | November 05, 2019  |  9:00am-12:00pm |
|   2    | November 05, 2019  |  12:00pm-3:00pm |
|   3    | November 05, 2019  |   3:00pm-6:00pm |
|   4    | November 05, 2019  |   6:00pm-9:00pm |
|   5    | November 06, 2019  |  9:00am-12:00pm |
|   6    | November 06, 2019  |  12:00pm-3:00pm |
|   7    | November 06, 2019  |   3:00pm-6:00pm |
|   8    | November 12, 2019  |  9:00am-12:00pm |
|   9    | November 13, 2019  |  9:00am-12:00pm |
+--------+--------------------+-----------------+

reservation_date is in VARCHAR format

TIME_AVAILABILITY
+--------+------------------+
|   id   | reservation_time |
+--------+------------------+
|   1    |  9:00am-12:00pm  |
|   2    |  12:00pm-3:00pm  |
|   3    |   3:00pm-6:00pm  |
|   4    |   6:00pm-9:00pm  |
+--------+------------------+

so here's the thing, I want to display the values in my TIME_AVAILABILITY table based on a $_POST variable and RESERVATIONS table. For example if a user's input is November 05, 2019 It will display nothing because all the values in TIME_AVAILABILITY table are already in the RESERVATIONS table, but if the user input is November 06, 2019 it will only display 6:00pm-9:00pm I tried using INNER JOIN but I just can't make it work

2 answers

  • answered 2019-11-14 05:47 WEBjuju

    The TIME_AVAILABILITY table joined to a subquery of the RESERVATIONS table will produce only the null matches (which is what you are looking for since null represents a TIME_AVAILABILITY that has not been filled for the date put in the where clause your "POST" date as it were):

    select t.reservation_time
    from TIME_AVAILABILITY t
      left join (
        select reservation_time, reservation_date
        from RESERVATIONS
        where reservation_date = 'November 05, 2019'
      ) as q
      on t.reservation_time = q.reservation_time
    where q.reservation_date is null; 
    

  • answered 2019-11-14 08:00 srijib

    Alternative to above answer you can do it with not in clause.

    select t.reservation_time
    from TIME_AVAILABILITY t where t.reservation_time not in (
    select reservation_time
        from RESERVATIONS
        where reservation_date = 'November 05, 2019'
    )