Common MySQL Queries

Appointments available

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; 

+more

Read more: Artful Software Development

QR: 

Posted via email from Jasper-net