0

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.

x74x61
  • 423
  • 6
  • 18

1 Answers1

1

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

Puggan Se
  • 5,738
  • 2
  • 22
  • 48
  • Thanks. I combined this with this [solution to populate the workdays table](http://stackoverflow.com/questions/4736690/create-mysql-date-range) – x74x61 Jul 01 '12 at 07:48