MySQL Left Join table where joined table can be null, but has where conditions

Question

What is the best way to go about left joining a table that has specific where conditions that must be met, but at the same time may also be null ?

Example:

Select a.*, b.schedule
from Tablea a 
    left join Tableb b on a.subid = b.subid
WHERE 
    b.date > NOW()
    and b.active = 1

It is possible that table B is empty, but if it is not empty then only active future schedules should show up.

My thoughts are something like "left join (select * from Tableb where date > now() and active = 1) b on a.subid = b.subid, but I am not sure if this will work — rstephen, Jan 29 '19 at 19:47
You don't say what you want. You don't give the "specific where conditions"; you give wrong code & give only a partial requirement. When clear this will be a faq. — philipxy, Feb 13 '20 at 23:27
Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Feb 13 '20 at 23:28

score 1 · Accepted Answer · answered Jan 29 '19 at 19:45

1

You just need to move the condition to the ON clause of the JOIN :

Select a.*, b.schedule
from Tablea a 
    left join Tableb b 
    ON a.subid = b.subid
    AND b.date > NOW()
    AND.active = 1

answered Jan 29 '19 at 19:45

GMB

score 1 · Answer 2 · answered Jan 29 '19 at 19:46

1

You should add the condition for left join column in ON clause otherwise work as inner join

    Select a.*, b.schedule
    from Tablea a 
    left join Tableb b on a.subid = b.subid AND  
        b.date > NOW()
        and b.active = 1

answered Jan 29 '19 at 19:46

ScaisEdge

score 0 · Answer 3 · answered Jan 29 '19 at 19:52

Just tested this and it worked, i'll keep this up incase anyone else has a better solution but this was what I came up with

Select a.*, b.schedule
from Tablea a 
    left join (select * from Tableb where date > NOW() and active = 1) b 
        on a.subid = b.subid
WHERE

3 Answers3