Posted by Hugo Kornelis on 01/18/06 22:47

On 18 Jan 2006 11:44:39 -0800, commanderjason@gmail.com wrote:

(snip)
>select max(username), count(answer) from tblUsers
>left outer join tblanswers on tblAnswers.userid = tblUSers.id
>where tblAnswers.answer = 4
>group by tblUsers.id

Hi commanderjason,

The outer join ensures that rows from tblUsers are retained even when
there's no match in tblAnswers. But this only applies to the ON
condition. If for a row from tblUsers, no row in tblAnswers satisfies
that condition (ie tblAnswers.userid = tblUsers.userid), then it will be
retained with NULL values for the columns from the tblAnswers table.

After that, the WHERE condition will throw away all rows produced by the
join that have tblAnswer other than 4 - including the ones with
tblAnswer set to NULL as a result of the outer join. Effectively, you
have negated the effect of the outer join and turned it back into an
inner join.

So far the theory. The answer is much easier: move the WHERE condition
to the ON clause.

select max(username), count(answer) from tblUsers
left outer join tblAnswers on tblAnswers.userid = tblUsers.userid
and tblAnswers.answer = 4
group by tblUsers.userid

(Note that I only had to change "where" to "and" here!)

--
Hugo Kornelis, SQL Server MVP

[Back to original message]