MySQL selecting entries when true and replace with null when false

I am programming an office schedule.

I have a table users in which each user has a regular schedule hreg that is monday to friday.

I have a table hleave in which I enter a leave code for the dates a user is out of office.

Table hleave:

hleave_id   user        dateStart   dateEnd     leaveCode
int(11)PK   int(11)FK   date        date        int(11)FK
1           5           2019-02-11  2019-02-13  1
2           1           2019-02-28  2019-02-28  1
3           3           2019-02-26  2019-02-28  2

Table users:

user_id     firstName   lastName    link_team_id    link_hreg_id
int(11)PK   varchar     varchar     int(11)FK       int(11)FK
1           Bob         Smith       1               1
2           Alice       Fraser      1               1
3           Jenny       Summers     1               1
4           Carl        Raisman     1               1
5           Roger       Wayne       1               1

Table teams:

team_id     teamName
int(11)PK   varchar
1           team 1
2           team 2
3           team 3

Table hreg:

hreg_id     weekStart   weekEnd
int(11)PK   int(11)     int(11)
1           1           5
2           1           4
3           2           5

Table leave_codes:

code_id     codeName
int(11)PK   varchar
1           A
2           B 
3           C
4           D

If there is an hleave entry with a date range that either starts this week, ends this week or starts before this week and ends after this week, I want to display the leave code.

If not, I want to display the user’s regular schedule “working”. For example:

        Monday  Tuesday Wednesday   Thursday    Friday
BOB     working working working     A           working
ALICE   working working working     working     working
JENNY   working B       B           B           working
CARL    working working working     working     working

Here is my query for TEAM #1 (I replaced the variables $monday and $friday with real dates for clarity) :

SELECT 
    users.firstName,
    users.lastName,
    users.link_team_id,
    users.link_hreg_id,
    hreg.weekStart,
    hreg.weekEnd,
    leave_codes.codeName,
    hleave.dateStart,
    hleave.dateEnd
FROM users
    LEFT JOIN hleave ON hleave.user = users.user_id
    JOIN teams ON users.link_team_id = teams.team_id 
    JOIN hreg ON users.link_hreg_id = hreg.hreg_id
    LEFT JOIN leave_codes ON hleave.leaveCode = leave_codes.code_id
WHERE
    (CASE 
        WHEN (hleave.dateStart BETWEEN '2019-02-25' AND '2019-03-01')
            THEN (users.link_team_id = 1)
        WHEN (hleave.dateEnd BETWEEN '2019-02-25' AND '2019-03-01')
            THEN (users.link_team_id = 1)
        WHEN (hleave.dateStart < '2019-02-25' AND hleave.dateEnd > '2019-03-01')
            THEN (users.link_team_id = 1)
        WHEN (hleave.dateStart IS NULL AND hleave.dateEnd IS NULL)
            THEN (users.link_team_id = 1)
    END)

Results:

firstName   lastName    link_team_id    link_hreg_id    weekStart   weekEnd     codeName    dateStart   dateEnd
Bob         Smith       1               1               1           5           A           2019-02-28  2019-02-28
Alice       Fraser      1               1               1           5           NULL        NULL        NULL
Jenny       Summers     1               1               1           5           B           2019-02-26  2019-02-28
Carl        Raisman     1               1               1           5           NULL        NULL        NULL

This query works. I get the codeName, dateStart and dateEnd for a user if it happens this week. If dateStart and dateEnd are NULL, I still get the user firstName, lastName and hreg so I can display them on the website as working.

So far so good, but I have a problem. When dateStart < '2019-02-25' and dateEnd < '2019-03-01' (i.e. if the leave happened before this week) I get nothing for this user since the date range validates as FALSE and it is not NULL either. For instance, if Roger had a leave on 2019-02-11 to 2019-02-13, Roger is not included in the results set.

Same thing goes for a date range in the future : dateStart > '2019-02-25' and dateEnd> '2019-03-01'.

What would be the best way to achieve this? I tried CASE WHEN in the SELECT clause to pin poit the dates I want to select but the result is the same. I either need to select differently or to turn entries validating as FALSE to NULL?

Answer

As I noted in my comment, it appears that you’re making the query a bit more complex than needed.

The main issue is the CASE/WHEN in your WHERE criteria, limiting the users to those matching in the CASE/WHEN, Since Roger was on leave, but did not match the specified dates in the WHERE criteria, he was excluded.

As you appear to only be interested in a specific team, the user team can be filtered directly in your main query. Presuming hleave only contains an entry when the user is on leave for the date range queried, the condition can be moved from your WHERE to the LEFT JOIN, displaying NULL when the user is not on leave.

SELECT 
users.firstName,
users.lastName,
users.link_team_id,
users.link_hreg_id,
hreg.weekStart,
hreg.weekEnd,
leave_codes.codeName,
hleave.dateStart,
hleave.dateEnd
FROM users
JOIN teams 
ON users.link_team_id = teams.team_id
JOIN hreg 
ON users.link_hreg_id = hreg.hreg_id
LEFT JOIN hleave 
ON hleave.user = users.user_id
AND (
    hleave.dateStart BETWEEN '2019-02-25' AND '2019-03-01' #leave starts this week
    OR
    hleave.dateEnd BETWEEN '2019-02-25' AND '2019-03-01' #leave ends this week
    OR
    (hleave.dateStart < '2019-02-25' AND hleave.dateEnd > '2019-03-01') #leave started before this week and continues after
)
LEFT JOIN leave_codes 
ON hleave.leaveCode = leave_codes.code_id
WHERE users.link_team_id = 1

Results in:

    <table>
      <thead>
        <tr>
          <th class="col0">firstName</th>
          <th class="col1">lastName</th>
          <th class="col2">link_team_id</th>
          <th class="col3">link_hreg_id</th>
          <th class="col4">weekStart</th>
          <th class="col5">weekEnd</th>
          <th class="col6">codeName</th>
          <th class="col7">dateStart</th>
          <th class="col8">dateEnd</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td class="col0">Bob</td>
          <td class="col1">Smith</td>
          <td class="col2">1</td>
          <td class="col3">1</td>
          <td class="col4">1</td>
          <td class="col5">5</td>
          <td class="col6">A</td>
          <td class="col7">2019-02-28</td>
          <td class="col8">2019-02-28</td>
        </tr>
        <tr>
          <td class="col0">Jenny</td>
          <td class="col1">Summers</td>
          <td class="col2">1</td>
          <td class="col3">1</td>
          <td class="col4">1</td>
          <td class="col5">5</td>
          <td class="col6">B</td>
          <td class="col7">2019-02-26</td>
          <td class="col8">2019-02-28</td>
        </tr>
        <tr>
          <td class="col0">Alice</td>
          <td class="col1">Fraser</td>
          <td class="col2">1</td>
          <td class="col3">1</td>
          <td class="col4">1</td>
          <td class="col5">5</td>
          <td class="col6"></td>
          <td class="col7"></td>
          <td class="col8"></td>
        </tr>
        <tr>
          <td class="col0">Carl</td>
          <td class="col1">Raisman</td>
          <td class="col2">1</td>
          <td class="col3">1</td>
          <td class="col4">1</td>
          <td class="col5">5</td>
          <td class="col6"></td>
          <td class="col7"></td>
          <td class="col8"></td>
        </tr>
        <tr>
          <td class="col0">Roger</td>
          <td class="col1">Wayne</td>
          <td class="col2">1</td>
          <td class="col3">1</td>
          <td class="col4">1</td>
          <td class="col5">5</td>
          <td class="col6"></td>
          <td class="col7"></td>
          <td class="col8"></td>
        </tr>
      </tbody>
    </table>