|
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.
Navigation:
[Reply to this message]
|