Left outer joins that don't return all the rows from T1
- by Summer
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.
