Using an OR condition in LEFT JOIN

Question

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?

Answer 1

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)