Microsoft SQL Server forum - Querying joined tables with 0 results

Author

Querying joined tables with 0 results

commanderjason@gmail.com

2006-01-19, 3:24 am

This seems like a very simple question but i have never been able to
find an easy answer to it.

I have a user table and i do a join with another table, we'll call the
other table a results table.

The results table has numerous rows with the userid foreign key.

I want to make a query that will give me the number of rows in the
results table for each user where the result is some value

The query is simple to make but will only show the users who have a
record in the results table the meet the where criteria, however i want
to display each user and show a record count of 0 when there are no
results in the results table that match the criteria.

for example i have 2 tables.

tblUsers
_______________
userid | username
--------------------------
1 | user1
2 | user2

tblAnswers
________________
userid | answer
----------------------------
1 | 1
1 | 0
1 | 4
2 | 1
2 | 0

if i run the query:

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

i just get

user1 | 1

i want to get

user1 | 1
user2 | 0

the only way ive found to do this is with a temp table and a curser to
create all the users records and go back through an insert the answer
count for each user. This approach seems very expensive and requires a
query that is 3 times larger than is needed for the same results
without including 0 count records. I know there must be a better way to
do this.

Any help is appreciated.

Hugo Kornelis

2006-01-19, 3:24 am

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

commanderjason@gmail.com

2006-01-20, 3:23 am

Thanks for the response, very helpful

Sponsored Links