I'm having a problem with my SQL command running on MariaDB.
I have the following tables:
|1||1||2021-05-17 13:20:00||2021-05-17 14:20:00||NULL|
|2||2||2021-05-19 08:00:00||2021-05-19 12:30:00||2|
|3||2||2021-05-14 10:00:00||2021-05-14 13:00:00||NULL|
I'm trying to join those tables by the
LEFT JOIN, however unsuccessfully.
This is the command I made and edited many times without success:
SELECT reservations.*, IF(DATE_FORMAT(reservations.time_from, '%Y-%m-%d') = CURDATE(), IF(reservations.user_id IS NULL, TRUE, FALSE), TRUE) AS removable FROM reservations LEFT JOIN ( SELECT users.id AS userID, users.name AS name, users.surname AS surname FROM users ) AS users ON reservations.user_id = users.userID WHERE reservations.time_from >= CURDATE() ORDER BY reservations.time_from;
However this is all I'm getting:
|3||2||2021-05-14 10:00:00||2021-05-14 13:00:00||NULL||1|
|1||1||2021-05-17 13:20:00||2021-05-17 14:20:00||NULL||1|
|2||2||2021-05-19 08:00:00||2021-05-19 12:30:00||2||1|
Basically I'm getting the first table without the data of the second table.
I do need to include rows with
user_id NULL as it is used for confirmation reasons. Am I missing something?
Here is the requested dbfiddle: dbfiddle
The column is
users.id but in the
LEFT JOIN SELECT query I rename the column to
user_id of the reservation is a column allowing NULL, as it is a user assigned for the reservation. NULL => no one has been assigned yet.
What I'm trying to do is to get all the reservation rows and users assigned by staff to the reservation. Imagine it as assigned guard for the child. That's why I need to get all upcoming reservations with information who the guard is going to be.
user_id is not NULL, the table should contain even the columns
users.surname since I'm selecting them in the join. That's what I'm looking for.