MySQL Appointments available

-- ---------------------------------------------------------------------------
-- Appointments available

-- from the Artful Common Queries page
-- http://www.artfulsoftware.com/infotree/qrytip.php?id=98
-- ---------------------------------------------------------------------------

-- Given a clinic of physicians, patients and appointments, how to find an
-- available appointment time for a given physician?

-- This is a variant of the [Not] Exists query pattern. Though we can write it
-- with subqueries, performance will be crisper with a join. But finding data
-- that is not there requires a join to data which is there. So in addition
-- to tables for appointments, doctors and patients, we need a table of all
-- possible appointment datetimes.

-- Here's a schema illustrating the idea ...

CREATE TABLE a_dt (         -- POSSIBLE APPOINTMENT DATES AND TIMES
  d DATE,
  t TIME
);
CREATE TABLE a_drs (        -- DOCTORS
  did INT                   -- doctor id
);
CREATE TABLE a_pts (        -- PATIENTS
  pid INT
);
CREATE TABLE a_appts (      -- APPOINTMENTS
  aid INT,                  -- appt id
  did INT,                  -- doctor id
  pid INT,                  -- patient id
  d DATE,
  t TIME
);

-- Now we can apply the [Not] Exists query pattern. To find free appointment
-- datetimes for a given doctor in a given datetime range, we left join
-- possible appointments to existing appointments on date and time and doctor,
-- add Where conditions for desired appointment datetimes, and finally add a
-- Where condition that the appointment slot be null, i.e. free...

SELECT d.did, a.d, a.t
FROM a_dt AS a
LEFT JOIN a_appts AS ap USING (d,t)
LEFT JOIN a_drs AS d
   ON a.d = ap.d
  AND a.t = ap.t
  AND ap.did = d.did
  AND ap.did = 1
WHERE a.d BETWEEN desired_start_date AND desired_end_date
  AND a.t BETWEEN desired_start_time AND desired_end_time
  AND ap.aid IS NULL;