Left outer joins that don't return all the rows from T1

Question

Left outer joins should return at least one row from the T1 table if it matches the conditions. But what if the left outer join performs a join successfully, then finds that another criterion is not satisfied? Is there a way to get the query to return a row with T1 values and T2 values set to NULL?

Here's the specific query, in which I'm trying to return a list of candidates, and the user's support for those candidates IF such support exists.

   SELECT c.id, c.name, s.support  
     FROM candidates c
LEFT JOIN support s on s.candidate_id = c.id
    WHERE c.office_id = 5059 
      AND c.election_id = 92 
      AND (s.user_id = 2 OR s.user_id IS NULL)  --This line seems like the problem
 ORDER BY c.last_name, c.name

The query joins the candidates and support table, but finds that it's a different user who supported this candidate (user_id=3, say). Then the candidate disappears entirely from the result set.

Answer 1

Can't you move the condition from the where to the join clause?

LEFT JOIN support s on s.candidate_id=c.id and s.user_id = 2