Mysql – LEFT JOIN not joining the second table

join;mariadbMySQL

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	one@email.com	admin	peter	peterson	123456789
3	two@email.com	assistant	john	johnson	NULL
4	three@email.com	admin	mike	mikeson	987654321
5	four@email.com	admin	leo	leoson	NULL
6	five@email.com	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.

Best Answer

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;

Related Solutions

Mysql – Left join not working like expected

If you run the statement without the where clause you'll see why:

lang | id    | lang | id   
-----+-------+------+------
en   | hello | en   | hello
en   | hello | fr   | hello
fr   | hello | en   | hello
fr   | hello | fr   | hello
en   | world | en   | world
en   | world | de   | world
de   | world | en   | world
de   | world | de   | world

The join on the "id" column works like this:

Take the first hello from the table and look for all rows that contain hello - that yield two rows for the first hello. The same happens with the second hello, so you wind up with 2x2 rows for the join on hello. And the same for world

The outer join does not play any role, because there is a match for each id (actually: two matches).

You can never get your first (intended) result because that implies that all rows in the "a" table have lang='en' (which is of course not true).

To get the missing translations you need to first create the combination of all languages and ids:

select distinct a.id, b.lang
from trans a
  cross join trans b;

Now you need to find all rows that are not in that result:

select *
from ( 
  select distinct a.id, b.lang
  from trans a
    cross join trans b
) ac
where not exists (select 1
                  from trans mt
                  where mt.id = ac.id 
                    and mt.lang = ac.lang);

You can achieve this with an outer join as well. I simply prefer the not exists because it documents more clearly the intention (and because I hardly ever work with MySQL which is known to perform poorly with sub-queries like that)

select ac.*
from ( 
  select distinct a.id, b.lang
  from trans a
    cross join trans b
) ac
  left join trans mt on mt.id = ac.id and mt.lang = ac.lang
where mt.id is null;

Here is an SQLFiddle: http://sqlfiddle.com/#!2/9804d/6

Edit

after testing the peformance with larger tables, it seems that Sean's version of the cross join is much more efficient than mine.

So this statement should be faster than the ones above:

select at.*
from (
    select lang_code, label_code
    from (
        SELECT distinct lang_code 
        FROM translations
    ) as translang
      cross join (
         SELECT distinct label_code 
         FROM translations
      ) as transid
) at
  left join translations mt 
         on mt.lang_code = at.lang_code
        and mt.label_code = at.label_code
where mt.lang_code is null;

Edit 2

And another version to be tested (SQL-Fiddle):

SELECT a.lang, a.id,
       l.lang AS blang
FROM trans a 
  CROSS JOIN 
    ( SELECT DISTINCT lang
      FROM trans
    ) l
  LEFT JOIN trans b 
    ON  b.id = a.id
    AND b.lang = l.lang 
WHERE a.lang = 'en'
  AND b.id IS NULL ;

MYSQL joining 3 tables with left join

I suggest you first read this question and answer Help with this query.

Then try to rewrite your queries using Option 3 (or all of the options). The following is a first attempt at option 1:

SELECT 
    p.id_pupil
  , p.name
  , p.surname
  , ROUND(AVG(q.calificacion),2) AS average
  , COUNT(DISTINCT q.id_qualification) AS count
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'miss'        THEN i.id_incidents END)
       AS misses
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'delay'       THEN i.id_incidents END)
       AS delays
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'attitude'    THEN i.id_incidents END)
       AS attitude
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'miss_justif' THEN i.id_incidents END)
       AS misses_justificadas
FROM 
    pupils                   AS p 
    LEFT JOIN incidents      AS i  ON  i.id_pupil = p.id_pupil
                                   AND i.id_trimester = 1
    LEFT JOIN qualifications AS q  ON  q.id_pupil = p.id_pupil
                                   AND q.id_trimester = 1
                                   AND q.type_qualification = 'class' 
WHERE 
    p.level = 1
    AND p.class = 'A' 
    AND p.id_trimester = 1 
GROUP BY 
    p.id_pupil 
  , p.name
  , p.surname ;

Best Answer

Related Solutions

Mysql – Left join not working like expected

MYSQL joining 3 tables with left join

Related Question