March 23, 2009 at 1:00 pm
I have two queries that are almost the same and I get the results that I want from one of them but not from the second one. I want the query to return a count of 0 if there were no responses. Here is the first query that works:
DECLARE @BDate DATETIME, @EDate DATETIME
SET @BDate = '1/1/2009'
SET @EDate = '12/31/2009'
SELECT tblMDD.Factor, ISNULL(SUM(CASE WHEN tblData.Response IS NOT NULL THEN 1 ELSE 0 END), 0) AS CountOfFactor, tblPart.PID, tblMDD.QID, tblMDD.EName
FROM tblMDD LEFT OUTER JOIN
tblData ON tblMDD.VID = tblData.VID AND tblMDD.EValue = tblData.Response LEFT OUTER JOIN
tblPart ON tblData.PID = tblPart.PID AND tblData.RID = tblPart.RID
WHERE (tblPart.DisDate BETWEEN @BDate AND @EDate) AND (tblPart.CallOutcome = N'Completed')
GROUP BY ALL tblMDD.QID, tblPart.PID, tblMDD.Factor, tblMDD.EName
HAVING (tblMDD.QID = 'Q1') AND (tblMDD.Factor < 5) AND (tblPart.PID = '340061')
ORDER BY tblMDD.Factor DESC
427340061Q1Always_
33340061Q1Usually_or
21340061Q1Sometimes
10340061Q1Never
I get the 0 in the second column that i am wanting since there were no responses.
Now the one that won't quite work:
SELECT tblMDD.Factor, ISNULL(SUM(CASE WHEN tblData.Response IS NOT NULL THEN 1 ELSE 0 END), 0) AS CountOfFactor, tblPart.PID, tblMDD.QID, tblMDD.EName,
Year(tblPart.DisDate) as YR
FROM tblMDD LEFT OUTER JOIN
tblData ON tblMDD.VID = tblData.VID AND tblMDD.EValue = tblData.Response LEFT OUTER JOIN
tblPart ON tblData.PID = tblPart.PID AND tblData.RID = tblPart.RID
WHERE (tblPart.CallOutcome = N'Completed')
GROUP BY ALL tblMDD.QID, tblPart.PID, tblMDD.Factor, tblMDD.EName, Year(tblPart.DisDate)
HAVING (tblMDD.QID = 'Q1') AND (tblMDD.Factor < 5) AND (tblPart.PID = '340061')
ORDER BY tblMDD.Factor DESC
and the results:
4124340061Q1Always_ 2006
4315340061Q1Always_ 2007
4213340061Q1Always_ 2008
427340061Q1Always_ 2009
312340061Q1Usually_or2006
348340061Q1Usually_or2007
321340061Q1Usually_or2008
33340061Q1Usually_or2009
24340061Q1Sometimes2006
214340061Q1Sometimes2007
26340061Q1Sometimes2008
21340061Q1Sometimes2009
15340061Q1Never 2008
Why am I not getting a count of 0 for the years 2006, 2007 and 2009 for the Factor of 1.
Thanks in advance!!
March 23, 2009 at 2:20 pm
I don't think the first query is working the way you think it is. By including in the where clause any column from the tables you have left outer joined to - you are effectively making those joins inner joins.
You can confirm that by viewing the execution plans.
In the first query, you are getting rows from tblPart where there is no data in tblData.Response.
In the second query, you are not getting any rows from tblPart, and since there are no rows from tblPart where the CallOutcome = 'Complete' you don't have any rows from tblData.
If you want additional help on this - please read the article in my signature.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 23, 2009 at 3:02 pm
Mr. Williams,
Thank you for your response. I am not sure that I follow you though, I apologize.
In the first query there actually is no data in tblData where the call was 'Completed' from tblPart for that Factor, QID and PID. So getting the count of 0 for Factor 1 is what I wanted. When I manually count the data in tblData for the other Factors I get the counts as shown.
On the second query the counts that I am getting are also correct if I manually count with the specified parameters. I'm just a little confused on where you say I am not getting any rows from tblPart where the CallOutcome = 'Completed' so I am not getting any rows from tblData. Where are the actual counts for everything else that is correct coming from?
Again I am sorry if I don't understand, my background is Access where I didn't write the queries so this is still new to me.
March 23, 2009 at 3:24 pm
By including the columns from the outer table in your joins, you are excluding any rows where there are no matches. This is the same as if you had specified inner joins.
SELECT ...
FROM tableA a
INNER JOIN tableB b ON b.key = a.key AND b.column1 = 'somevalue'
WHERE a.column1 = 'somevalue';
In the above, we are going to get rows that match the join criteria before we filter out the columns in the where clause.
SELECT ...
FROM tableA a
LEFT JOIN tableB b ON b.key = a.key
WHERE a.column1 = 'somevalue'
AND b.column1 = 'somevalue';
This query is logically the same as the inner join and will return the same results. By including the column from tableB in the where condition you are eliminating the rows where there is no match from tableB.
SELECT ...
FROM tableA a
LEFT JOIN tableB b ON b.key = a.key AND b.column1 = 'somevalue'
WHERE a.column1 = 'somevalue';
This one is not the same and will include all rows from tableA where column1 = 'somevalue', including those rows where there is no matching row in tableB. Those rows with no matching data will have null values for all columns from tableB.
In your queries, you are using columns from your outer tables in the where clause. Because of this, you are eliminating those rows where there is no matching data. There must be data in tblPart that matches the where clause to be included in the results - if there is no data, there would not be any rows returned.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply