May 28, 2008 at 9:08 am
As it stands, the current stored procedure looks a lot like this:
SELECT C.AnswerText, COUNT(C.AnswerValue) AS 'Count'
FROM dbo.SurveyResults AS R INNER JOIN
dbo.SurveyQuestions AS Q ON R.QuestionID = Q.QuestionID INNER JOIN
dbo.SurveyChoices AS C ON R.AnswerID = C.AnswerID INNER JOIN
dbo.InstallCardCustomerInformation AS I ON R.InstallCardID = I.InstallCardId
WHERE (Q.QuestionID = 6) AND (I.InstallersMemberID = 'D2018672')
GROUP BY C.AnswerText
Basically, this takes a member and a question ID, gets a list of the answers for that question, and returns a count of how many times that answer has been selected for that question relating to that member. The issue is that right now, sometimes nobody has chosen a particular answer to a question. In this case, the answer text isn't even returned. I need every possible answer to be returned, regardless of whether or not it's been chosen. I've been playing around, but I can't seem to figure it out. Any suggestions?
__________________________________
I went left where the road went right... I kept going when everyone said it was impossible... I defied everything they said couldn't be done...
And now I'm lost...
May 28, 2008 at 9:17 am
Yes, use OUTER JOINs instead of INNER JOINs where appropriate.
John
May 28, 2008 at 9:22 am
And where would be appropriate? I'm a bit of a T-SQL noob.. my supervisor usually handles most of the complex SQL stuff, and I do all the .NET development work...
__________________________________
I went left where the road went right... I kept going when everyone said it was impossible... I defied everything they said couldn't be done...
And now I'm lost...
May 28, 2008 at 11:16 am
Try this
SELECT C.AnswerText, COUNT(C.AnswerValue) AS 'Count'
FROM dbo.SurveyResults AS R
INNER JOIN dbo.SurveyQuestions AS Q
ON R.QuestionID = Q.QuestionID
INNER JOIN dbo.InstallCardCustomerInformation AS I
ON R.InstallCardID = I.InstallCardId
LEFT JOIN dbo.SurveyChoices AS C
ON R.AnswerID = C.AnswerID
WHERE (Q.QuestionID = 6) AND (I.InstallersMemberID = 'D2018672')
GROUP BY C.AnswerText
This query will work based on the assumption that even though there is no answer chosen, there will be a row inserted in SurveyResults with AnswerID as NULL.
May 28, 2008 at 11:27 am
AnswerID will never be null. AnswerID is an index that looks up against SurveyChoices. Maybe I didn't make the layout totally clear... wouldn't be the first time...
Using the parameters I supplied, there are two rows returned, which is correct in the sense that those are the only two answers out of SurveyChoices that are referenced by the AnswerID field. The problem is that there are three entries in SurveyChoices that are tied to QuestionID = 6, and I need that third answer to be returned with a Count of 0. None of the data fields that exist in ANY of the tables will ever be null.
Does that make more sense?
__________________________________
I went left where the road went right... I kept going when everyone said it was impossible... I defied everything they said couldn't be done...
And now I'm lost...
May 28, 2008 at 11:38 am
chrono34 (5/28/2008)
AnswerID will never be null. AnswerID is an index that looks up against SurveyChoices. Maybe I didn't make the layout totally clear... wouldn't be the first time...Using the parameters I supplied, there are two rows returned, which is correct in the sense that those are the only two answers out of SurveyChoices that are referenced by the AnswerID field. The problem is that there are three entries in SurveyChoices that are tied to QuestionID = 6, and I need that third answer to be returned with a Count of 0. None of the data fields that exist in ANY of the tables will ever be null.
Does that make more sense?
Try this on for size. The answer is in the LEFT OUTER JOIN, the point is to find the right way to set it up. It also involves moving one of the WHERE clauses into your join criteria.
SELECT C.AnswerText, COUNT(C.AnswerValue) AS 'Count'
FROM dbo.SurveyResults AS R
INNER JOIN
dbo.SurveyQuestions AS Q
ON R.QuestionID = Q.QuestionID
INNER JOIN
dbo.SurveyChoices AS C
ON R.AnswerID = C.AnswerID
LEFT OUTER JOIN
dbo.InstallCardCustomerInformation AS I
ON R.InstallCardID = I.InstallCardId
AND (I.InstallersMemberID = 'D2018672')
WHERE (Q.QuestionID = 6)
GROUP BY C.AnswerText
Leaving the InstallersMemberID in the WHERE clause would negate changing the join type to LEFT OUTER.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 28, 2008 at 11:46 am
I gave that a try, but now it returns total results that are way off from what I need... And by way off, I mean the results *should* be 'Not Satisfied = 0, Satisfied = 4, Very Satisfied = 3'... what your query returned was 'Not Satisfied = 707, Satisfied = 1842, Very Satisfied = 3940'...
__________________________________
I went left where the road went right... I kept going when everyone said it was impossible... I defied everything they said couldn't be done...
And now I'm lost...
May 28, 2008 at 12:18 pm
chrono34 (5/28/2008)
I gave that a try, but now it returns total results that are way off from what I need... And by way off, I mean the results *should* be 'Not Satisfied = 0, Satisfied = 4, Very Satisfied = 3'... what your query returned was 'Not Satisfied = 707, Satisfied = 1842, Very Satisfied = 3940'...
hmm...That means you have a whole truckload of things with no valid link to InstallCardInfo.
One thing that's puzzling - why is there no link between question and answer? That seems to be missing from the join criteria.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 28, 2008 at 12:48 pm
The link is through the Results table... actually, all of it is based on the results table. I'm honestly not even sure what's going on with this anymore...
__________________________________
I went left where the road went right... I kept going when everyone said it was impossible... I defied everything they said couldn't be done...
And now I'm lost...
May 28, 2008 at 12:53 pm
chrono34 (5/28/2008)
The link is through the Results table... actually, all of it is based on the results table. I'm honestly not even sure what's going on with this anymore...
I understand that link - there should still be some relationship showing which answers are valid for specific questions. As in - what answers are allowable for question1? which ones are valid for question2?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 28, 2008 at 1:05 pm
Ahh.. SurveyChoices.QuestionID binds to SurveyQuestions.QuestionID.
__________________________________
I went left where the road went right... I kept going when everyone said it was impossible... I defied everything they said couldn't be done...
And now I'm lost...
May 28, 2008 at 1:20 pm
All right then - let's try flipping this around a bit:
SELECT C.AnswerText, COUNT(C.AnswerValue) AS 'Count'
FROM
dbo.SurveyQuestions AS Q
INNER JOIN
dbo.SurveyChoices AS C
ON q.questionID=c.questionID
LEFT OUTER JOIN
(
select R_in.*
from dbo.SurveyResults AS R_in
inner join dbo.InstallCardCustomerInformation AS I
ON R_in.InstallCardID = I.InstallCardId
WHERE (I.InstallersMemberID = 'D2018672')
) R
ON R.QuestionID = Q.QuestionID
AND R.AnswerID = C.AnswerID
WHERE (Q.QuestionID = 6)
GROUP BY C.AnswerText
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 28, 2008 at 1:27 pm
I'll admit to having very little clue what's going on there... but it returns a count of 1 for Not Satisfied, where it should be 0...
__________________________________
I went left where the road went right... I kept going when everyone said it was impossible... I defied everything they said couldn't be done...
And now I'm lost...
May 28, 2008 at 1:30 pm
bleh. I changed the COUNT to (R.AnswerID) and it worked fine. Thanks a billion, although I still have little idea what it's doing... if you could clear that up for me, I might be able to use similar logic in a different stored proc that's currently having the same problem...
__________________________________
I went left where the road went right... I kept going when everyone said it was impossible... I defied everything they said couldn't be done...
And now I'm lost...
May 28, 2008 at 1:50 pm
Yup - messed up the field to count. Too much copying and pasting.
Essentially:
- the first two tables (questions/answers) help establish all of the possible choices. Meaning - the answers and all possible relevant answers to those questions.
- you then match that up to only the survery results that you are interested in counting (the survey results from the specific InstallerMemberID). You use the LEFT OUTER JOIN, since you don't want to start excluding survey answers.
- you then count something on the RIGHT side of the outer join, so that it will tell you how many people chose that possible answer. Count of a column returns a count of all non-null instances of that column, so a group that is null ( R.answerID would be null if that particular question was never picked by anyone for that particular question) would return 0.
Happy it's working for you.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply