vendredi 8 mai 2015

Empty result in association parent-children when there are not children

I have the following schema:

hours table: this table has "constant" data, it never changes because only will store the schedule-able hours

hour (int)
----
8
9
10

appointments table

hour (int)  |  date (text)
--------------------------
10          | 25/08/2015

In my application I want to show only available hours to set a new appointment based in hour-date filter. For example, I can say that for the days:

  • 25/08/2015: available hours are 8 and 9 because 10 is already taken
  • 26/08/2015: available hours are 8, 9 and 10 because there are not appointments at that date.

At the beginning I was using this query:

select h.hour 
from hours h, appointment a 
where h.hour != a.hour and a.date = 'the-date';

This query only works if there are appointments in the given dates, but for the rest of dates without appointments it returns empty result. I can achieve this task via application, but I am trying to exhaust all db's possibilities.

Aucun commentaire:

Enregistrer un commentaire