mysqldatecalendartimeslots

Determining free timeslots based on two tables in MySQL

So I have two tables.

Table 1 is named 'appointment' with the following fields: ID (int), TimeboxID (int), Date (date)

Table 2 is named 'timebox' with the following fields: ID (int), Weekday (int), StartTime (varchar)

Table 1 keeps all appointments (Date + TimeboxID combo), while table 2 defines which timeslots are available for each day of the week.

Here's my question: How can I find out, in 1 query, which dates have free timeslots available (and how many)?

ps. Field StartTime is only needed for display, it has no meaning here.

Solution

  • I should use somthing like:

    SELECT workdays.date, COUNT(DISTINCT timebox.ID) AS FreeTimeBoxes
FROM workdays
LEFT JOIN timebox ON (WEEKDAY(workdays.date) = timebox.Weekday)
LEFT JOIN appointment ON (workdays.date = appointment.date AND timebox.ID = appointment.TimeboxID)
WHERE appointment.ID IS NULL
GROUP BY workdays.date

    Don't know if you replace the table workdays whit something automated