Left Join problem with where AND

Question

Here is some hardcoded syntax. IPAddr is an int, this is sqlite and will be ported to mysql.

The syntax doesnt work with AND V.IPAddr <> 0. Possibly because V is a left join and may not exist (null?). How do I get it to to succeed when V == null || V.IPAddr <> Val?

select Post.id, name, body,
      (select count() from Votes where id=Post.id AND (type & 4)<> 0) as DV
   from Post 
left join Votes as V on V.id=Post.id
   where flag='1'  AND V.IPAddr <> 0 AND DV<1
   limit 1

Answer 1

Move the outer filter to the JOIN condition. Otherwise, the filter changes it to a INNER JOIN

select Post.id, name, body,
(select count() from Votes where id=Post.id AND (type & 4)<> 0) as DV
from Post 
left join Votes as V on V.id=Post.id AND V.IPAddr <> 0 
where flag='1' AND DV<1
limit 1

Answer 2

Gbn's solution is the best, but here are two other solutions. You can use a subquery:

from      Post p
left join (
          select  *
          from    Votes
          where   IPAddr <> 0
          ) v
on        v.id = p.id

Or an improved where clause:

from      Post p
left join Votes v
on        v.id = p.id
where     (IPAddr <> 0 or IPAddr is null)