473,320 Members | 1,921 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Querying joined tables with 0 results

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
2 1405
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
Thanks for the response, very helpful

Jan 20 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Vishant | last post by:
Hi, I'm having a problem getting the values from an OCIFetchStatement. Using the following codes, I'm geting the results from two table joined and I have to print out the results in such a ...
2
by: Elliot Rodriguez | last post by:
As I continue to read more about the benefits of database querying using ADO.NET, I am having a more difficult time distinguishing what the best approach to data retrieval is anymore. When...
0
by: roiavidan | last post by:
Hi, I'm having a bit of a problem with a small application I wrote in C#, which uses an Access database (mdb file) for storing financial data. After looking for a similiar topic and failing to...
3
by: loosecannon_1 | last post by:
I get a 90-120 second blocking when send 15 or so simultaneous queries to SQL Server 2000 that query a view made up of two joined tables. After each query is blocking for the same amount of time...
4
by: Jake | last post by:
I'm building a database of students whose hearing will be tested. I had a main table set up of all of the student data and then a test table linked to the main table by a student ID. The way...
5
by: sql_er | last post by:
Guys, I have an XML file which is 233MB in size. It was created by loading 6 tables from an sql server database into a dataset object and then writing out the contents from this dataset into an...
6
by: SNOWcondor | last post by:
Hi, I'm having trouble with a query and have been searching through numerous sites but am struggling to find anything or know exactly what to look for. Here's the scenario. I have two tables,...
2
by: KTosser | last post by:
I have two tables, one contains contacts and the other has all the events and years that the contacts participated in. What I want is to be able to choose the years, say 2005 and 2006, and get all...
1
by: SilRay | last post by:
I will admit up front that this is to help with an assignment for a class, however, I'm looking for information outside the scope of the assignment because I want to do something cooler. The...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.