Posted by commanderjason on 01/18/06 21:44

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.

[Back to original message]