两个IF语句并加入MYSQL

Question

I`ve been trying to make a dayplanner for a meeting room. I have two tables:

1) dayplanner: day split in 5 minute intervals Values=00:00:00,00:05:00,00:10:00 ... 23:55:00

2) reservations: name, start and finish Values=[john,12:00:00,12:30:00],[bob,14:00:00,14:45:00],etc

My goal is to use one query with two IF statements so i can check the starttime and what lies in between

Like this:

[Time] [reserved] [inbetween]

12:00:00 true true
12:05:00 true true
............................

12:35:00 false false

$query = "SELECT *,
          IF(d.dayplanner_time = a.reservations_starttime,'true','false') AS reserved,
          IF(d.dayplanner_time > a.reservations_start  && d.dayplanner_time <  a.reservations_finish,'true','false') AS inbetween
          FROM dayplanner d 
          LEFT JOIN reservations a 
         ON d.day_time = a.reservations_start OR d.dayplanner_time = a.reservations_finish
          WHERE d.dayplanner_time BETWEEN '12:00:00' AND '18:00:00';";

The query gives only one true in the reserverd column but nothing works. I hope you can help me.

Answer 1

You're matching your time intervals against reservations that either start or end exactly on that interval. By definition, if times are equal they can't be greater nor less than themselves, which means you're explicitly leaving out the reservations that don't match exactly that time interval.

The comparison should be

SELECT d.dayplanner_time,d.day_time,
      IF(d.dayplanner_time = a.reservations_starttime,'true','false') AS reserved,
      IF(a.reservations_start is null, false, true) AS inbetween
      FROM dayplanner d 
      LEFT JOIN reservations a 
      ON (d.day_time >= a.reservations_start AND d.dayplanner_time <= a.reservations_finish)
      WHERE d.dayplanner_time BETWEEN '12:00:00' AND '18:00:00';

which means "if there's at least one reservation starting before me, and ending before me, then there's something in between".

remember that when doing a LEFT JOIN, everything that is not null means they match your WHERE clause. That's why I only say in_between is false when reservations start is null.