Outer Join & WHERE Clause Problem

Question

Outer Join & WHERE Clause Problem

dec_obrien

Default port

Points: 1486
More actions
February 28, 2012 at 9:21 am

#391676

Hi,
I have an Applications table and a Visit Time table. I want to count the number of Applications for a given Event and display them grouped by the Visit Time. Here's what I have:
Application Table (cut-down version)
CREATE TABLE [dbo].[Applications](
[ApplicationID] [int] IDENTITY(10000,1) NOT NULL,
[App_FK_EventID] [int] NULL,
[App_TimeStart] [int] NULL
Visit Time Table (cut-down version)
CREATE TABLE [dbo].[TestTable](
[Timeslot] [int] NOT NULL
The Join between the two tables is on Applications.App_TimeStart = TestTable.Timeslot
Here's what I've tried:
SELECTTestTable.Timeslot,
COUNT(Applications.App_FK_EventID) AS NumInTimeSlot
FROM TestTable LEFT OUTER JOIN
Applications ON TestTable.Timeslot = Applications.App_TimeStart
GROUP BYTestTable.Timeslot
This works until I try to filter on App_FK_EventID. I've tried:
WHERE IsNull(App_FK_EventID, 1382) = 1382 but it doesn't return all of the rows from the TestTable where there are no corresponding entries in the Applications table i.e. NumInTimeSlot = 0.
This is what I'd like to get:
TimeSlotNumInTimeSlot
60
70
82
952
1060
1151
1230
130
1464
1555
1622
171
Can anyone help with my filter? Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 1

Its because the query is doing exactly what you are telling it to do and only returning records WHERE IsNull(App_FK_EventID, 1382) = 1382

This should work for you

I haven't tested this however as there is no test data

SELECT

TestTable.Timeslot

,SUM(CASE WHEN IsNull(Applications.App_FK_EventID, 1382) = 1382 THEN 1 ELSE 0 END) AS NumInTimeSlot

FROM

TestTable

LEFT JOIN Applications

ON TestTable.Timeslot = Applications.App_TimeStart

GROUP BY

TestTable.Timeslot

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

dec_obrien Default port Points: 1486 More actions · Answer 2

Hi,

I partly guessed it was dong what I told it to do: it's just that I was telling it the wrong thing!!!

I've tried your solution and it works perfectly,

Thanks again.

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

Your welcome - Glad to be of help 🙂

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe