Using an OR condition in LEFT JOIN

Here's what I have in 2 tables.

TABLE tbl_appuntamento => This table contains a list of appointments

    +------+----------------------+-----------+---------+
    | id   | data                 | slot      | id_sede |
    +------+----------------------+-----------+---------+
    | 1    | 2017-03-27           | 10:00:00  | 1       |
    | 2    | 2017-03-27           | 10:00:00  | 1       |
    | 3    | 2017-03-28           | 11:00:00  | 1       |
    | 4    | 2017-03-28           | 12:00:00  | 1       |
    +------+----------------------+-----------+---------+

TABLE tbl_blocco_operativo => This table contains a list of dates or slots in which no appointment can be set

    +------+----------------------+-----------+---------+------------+
    | id   | data                 | slot      | id_sede | is_fullday |
    +------+----------------------+-----------+---------+------------+
    | 1    | 2017-03-27           | 10:00:00  | 1       |0           |
    | 2    | 2017-03-27           | 11:00:00  | 1       |0           |
    | 3    | 2017-03-28           | 00:00:00  | 1       |1           |
    +------+----------------------+-----------+---------+------------+

I have this query

    SELECT appuntamento.*,blocco.slot blockSlot, blocco.is_fullday blockFullday 
    FROM tbl_appuntamento appuntamento 
    LEFT JOIN tbl_argomento argomento ON argomento.id = appuntamento.id_argomento
    LEFT JOIN  tbl_blocco_operativo blocco ON blocco.data = appuntamento.data AND blocco.id_sede = appuntamento.id_sede 
        AND ((blocco.is_fullday = 0 AND blocco.slot = appuntamento.slot) 
             OR (blocco.is_fullday = 1 AND blocco.slot <> appuntamento.slot))
    WHERE appuntamento.id_sede = :locationId
    AND appuntamento.data >= :startDate
    AND appuntamento.data <= :endDate
    GROUP BY appuntamento.id
    ORDER BY appuntamento.data, appuntamento.slot

The goal is to insert the columns "is_fullday" and "slot" from the second table in the results, to check if there is any conflicts with the schedule, since the appointment could be booked before anyone creates a record in the table "blocco_operativo"

Now this seems to work, but I'm not really sure the "OR" condition in the second JOIN is the best solution. Is it ok, or I'm missing something?

Solution

You can replace the OR expression with this:

blocco.is_fullday = (blocco.slot <> appuntamento.slot)

This works because the boolean expression between parenthesis will correspond to a number 0 or 1, which is exactly what you want blocco.is_fullday to be compared to.

In a more verbose way, this can be written as:

blocco.is_fullday = case blocco.slot when appuntamento.slot then 0 else 1 end

The Full Day case

In case the full day is blocked you may consider the slot irrelevant. In that case the condition could be reduced to:

(blocco.is_fullday = 1 OR blocco.slot = appuntamento.slot)

Since is_fullday acts like a boolean, you may say:

(blocco.is_fullday OR blocco.slot = appuntamento.slot)