issue with selecting 1 or 0 in mysql db

Question

I am having trouble with the following SQL statement. I have had this issue before but I can't remember how I fixed the problem. I am guessing the issue with this is that MySQL sees 0 as null? Note I didn't show the first part of the Select statement as it's irrelevant. My SQL works. It's showing rows with toffline that are = to 1 as well as 0...

FROM table1 AS tb1
LEFT JOIN table2 AS tb2 ON tb2.id = tb1.id2
LEFT JOIN table3 AS tb3 ON tb3.id = tb1.id3
AND tb1.toffline = 0

I have also tried AND NOT tb1.toffline = 1 also WHERE tb1.toffline = 0 all with the same result...

Answer 1

This is where indenting helps to see the issue. Your filtering is in the On clause not in a Where clause.

Select ..
From Table1 As tb1
    Left Join Table2 As tb2
        On tb2.Id = tb1.Id
    Left Join Table3 As tb3
        On tb3.id = tb1.id3
            And tb1.toffline = 0

Thus, the system is going to filter Table3 before it evaluates the Left Join to Table1. If what you are attempting is to get all results where toffline is zero, then move that last filter to the Where clause:

Select ..
From Table1 As tb1
    Left Join Table2 As tb2
        On tb2.Id = tb1.Id
    Left Join Table3 As tb3
        On tb3.id = tb1.id3
Where tb1.toffline = 0

EDIT Noticed that toffline is from Table1.

Answer 2

I am guessing the issue with this is that mysql sees 0 as null ?

No, MySQL treats 0 and NULL as different things. NULLs aren't even considered values at all.

It's possible that the first part of your query is actually relevant. Are you SURE that what you're seeing in the results is tb1.toffline, or could it be something else?

Consider this example:

SELECT tb1.toffline AS offline,
       tb2.toffline AS offline
FROM table1 AS tb1
LEFT JOIN table2 AS tb2 ON tb2.id = tb1.id2
LEFT JOIN table3 AS tb3 ON tb3.id = tb1.id3
AND tb1.toffline = 0

If you execute that query, the "offline" you see might be coming from tb1 or it might be coming from tb2. That's probably not the mistake you made, but let's see your entire query.