By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,307 Members | 2,102 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,307 IT Pros & Developers. It's quick & easy.

Querying joined tables with 0 results

P: n/a
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.

Jan 18 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On 18 Jan 2006 11:44:39 -0800, co************@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
Jan 18 '06 #2

P: n/a
Thanks for the response, very helpful

Jan 20 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.