Join 3 MySQL tables

November 3, 2022

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, 'test@test.com',    '1',    '1'),
(2, 'test2@test.com',   '1',    '2'),
(3, 'test3@test.com',   '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');

>Solution :

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