I have the following 3 MySQL tables.
I am storing bookings in the bookings table & the event dates and times in separate MySQL tables.

I want to have a MySQL query to list available times for a specific date.

So if I enter date of value 1 it'll show no times available but if I enter 2 it'll output 1 | 9:00.

INSERT INTO `bookings` (`id`, `email`, `date_requested`, `time_requested`) VALUES
(1, '[email protected]',    '1',    '1'),
(2, '[email protected]',   '1',    '2'),
(3, '[email protected]',   '2',    '2');

INSERT INTO `bookings_dates` (`id`, `date`) VALUES
(1, '2022-11-05'),
(2, '2022-11-06'),
(3, '2022-11-07');

INSERT INTO `bookings_times` (`id`, `time`) VALUES
(1, '9:00'),
(2, '9:15');

CodePudding user response：

You can use a NOT IN condition.

SELECT *
FROM bookings_times
WHERE id NOT IN (
    SELECT time_requested
    FROM bookings
    WHERE date_requested = (
        SELECT id 
        FROM bookings_dates 
        WHERE date = '2022-11-05'
    )
)

DEMO

CodePudding user response：

In order to get times for a given day (1) you can use the following query :

SELECT 
  t.`id`, t.`time`
FROM 
  bookings_times AS t
  INNER JOIN bookings AS b ON b.time_requested = t.id
WHERE
  b.date_requested = 1

Edit1:

In order to get times not in use for a given day (1) you can use the following query :

SELECT 
  t.`id`, t.`time`
FROM 
  bookings_times AS t
  LEFT JOIN bookings AS b ON b.time_requested = t.id AND (b.date_requested = 1)
WHERE
  IsNull(b.time_requested)