1

I'm having a problem with my SQL command running on MariaDB.

I have the following tables:

reservations:

id child_id time_from time_to user_id
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

users:

id email role name surname contact
2 [email protected] admin peter peterson 123456789
3 [email protected] assistant john johnson NULL
4 [email protected] admin mike mikeson 987654321
5 [email protected] admin leo leoson NULL
6 [email protected] admin jane janeson NULL

I'm trying to join those tables by the reservations.user_id and users.id using 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:

id child_id time_from time_to user_id removable
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 userID. 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.

If the user_id is not NULL, the table should contain even the columns users.name and users.surname since I'm selecting them in the join. That's what I'm looking for.

0

2 Answers 2

3

After clarification in the fiddle you provided, the only other way I can interpret your issue is you're saying you're not seeing the columns for the users table even though you joined to it. That's because you didn't list any columns from the users table in the SELECT list.

You can add any or all columns from your users subquery you want to the SELECT list like so:

SELECT reservations.*, IF(DATE_FORMAT(reservations.time_from, '%Y-%m-%d') = CURDATE(), IF(reservations.user_id IS NULL, TRUE, FALSE), TRUE) AS removable,
    users.*
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; 

Note you shouldn't use * in your SELECT list as it's bad practice and can lead to dependency issues later on, and rather you should explicitly list the column names you want.

Furthermore it seems like you might've gotten a little confused because you used a subquery to join in the users table but that's not necessary, rather you can re-write your query simpler like this:

SELECT reservations.*, IF(DATE_FORMAT(reservations.time_from, '%Y-%m-%d') = CURDATE(), IF(reservations.user_id IS NULL, TRUE, FALSE), TRUE) AS removable,
    users.id AS userID, users.name AS name, users.surname AS surname
FROM reservations
LEFT JOIN users
    ON reservations.user_id = users.id
WHERE reservations.time_from >= CURDATE()
ORDER BY reservations.time_from;
2
  • 1
    Right! Yes, great! Thank you, that's the piece I was missing :)
    – Nejento
    May 13, 2021 at 17:21
  • @Nejento Great, no problem! Also I slid in one more update that simplifies your query too. 🙂
    – J.D.
    May 13, 2021 at 17:25
0

Basically I'm getting the first table without the data of the second table.

Basically, that is the feature that LEFT provides.

Remove LEFT and see if you get what you expected.

Also, simplify the query as J.D. suggested.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.