Re: Need some SQL help!
Ok, I ALMOST got it! The results that are incorrect is just the number of answers per question. Here's what the query returns: http://wtomlinson.com/evalQRYs/qryData.jpg Look at numAnswers. I completed two evaluations. But numAnswers should be the total count of each answer that was given. I obviously didn't answer twice for every answer as shown in the query results. I need it to count up the number of answers that were chosen, for each answer, for each question. Here's my query as it stands now: ALMOST! SELECT Q.questionID, Q.questionsetID, Q.question, Q.answersetid, ARS.answersetid, ARS.answersetname, A.answerID, A.answersetID, A.answername, A.answersortnum, A.answervalue, EAR.evalID, EAR.answerID, E.evalID, E.InstructorID, COUNT(EAR.answerID) AS numAnswers FROM tblquestions Q INNER JOIN tblanswersets ARS USING (answerSetID) INNER JOIN tblanswers A USING (answerSetID) LEFT JOIN tblevalanswerresults EAR USING (questionID) INNER JOIN tblEvaluations E ON E.evalID = EAR.evalID WHERE E.InstructorID = '1021338' GROUP BY Q.questionID, A.answerID Thanks, Will ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273191 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need some SQL help!
I may have figured it out... I used this for my group by. GROUP BY Q.questionID, A.answerID Still needta test it more tho but my results are lookin better. Thanks Jochem! Will ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273182 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need some SQL help!
Jochem, this is close but not quite giving me the results I'm after. Here's the data as it looks in the table. I answered three questions, each had a choice of 5 answers. I chose 1 answer for each question. Here's the data as it appears in the tblEvalAnswerResults many table. http://wtomlinson.com/evalQRYs/tableData.jpg Here's the qry: SELECT Q.questionID, Q.questionsetID, Q.question, Q.answersetid, ARS.answersetid, ARS.answersetname, A.answersetID, A.answername, A.answersortnum, A.answervalue, EAR.evalID, EAR.answerID, A.answerID, COUNT(EAR.answerID) FROM tblquestions Q INNER JOIN tblanswersets ARS USING (answerSetID) INNER JOIN tblanswers A USING (answerSetID) LEFT JOIN tblevalanswerresults EAR USING (answerID) GROUP BY A.answerID Here's the data the query returns: http://wtomlinson.com/evalQRYs/qryData.jpg Notice how it's indeed returning 5 rows - 1 per answer choice. But It's also just returning 1 question. There were 3 questions answered. I've played with it some and will keep trying. Any ideas? Thanks much, Will ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273179 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need some SQL help!
>Will Tomlinson wrote: >> >> Do all these individual queries go into one query, separated with a ; ? > >Just run the last one, the others are to show how you build it step by step. > duhh.. :) Thanks Jochem! Will ~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273121 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need some SQL help!
Will Tomlinson wrote: > > Do all these individual queries go into one query, separated with a ; ? Just run the last one, the others are to show how you build it step by step. Jochem ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273113 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need some SQL help!
select * from ( SELECT ... FROM tblquestions ) questions, ( SELECT ... FROM tblquestions INNER JOIN tblanswersets USING (answerSetID) INNER JOIN tblanswers USING (answerID) ) answers, ( SELECT A.answerID, COUNT(EAR.answerID) FROM tblquestions Q INNER JOIN tblanswersets ARS USING (answerSetID) INNER JOIN tblanswers A USING (answerSetID) LEFT JOIN tblevalanswersresults EAR USING (answerID) GROUP BY A.answerID ) counts then you join your 3 sub queries down here.. On 3/20/07, Will Tomlinson <[EMAIL PROTECTED]> wrote: > Jochem, > > Do all these individual queries go into one query, separated with a ; ? > > Thanks, > Will > > ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273110 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Need some SQL help!
Jochem, Do all these individual queries go into one query, separated with a ; ? Thanks, Will ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273093 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need some SQL help!
>> Does this make sense? > >If you join this to some table with instructors you should get what you >need. > >Jochem Yes Jochem. I'm remote right now, but I'll give this a shot later today and let'cha know how it turns out. Thanks a ton dude! Will ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273078 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need some SQL help!
Will Tomlinson wrote: > > Show me a list of questions SELECT ... FROM tblquestions > all possible evaluation answers for each question SELECT ... FROM tblquestions INNER JOIN tblanswersets USING (answerSetID) INNER JOIN tblanswers USING (answerID) > and a total of each answer chosen. SELECT A.answerID, COUNT(EAR.answerID) FROM tblquestions Q INNER JOIN tblanswersets ARS USING (answerSetID) INNER JOIN tblanswers A USING (answerSetID) LEFT JOIN tblevalanswersresults EAR USING (answerID) GROUP BY A.answerID > Does this make sense? If you join this to some table with instructors you should get what you need. Jochem ~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273070 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need some SQL help!
On 3/19/07, Will T wrote: > MySQL 5. > > I'm building a dynamic question survey form. Here's my db schema: Honestly, I would probably do it in CF with loops, vs. a nifty SQL query. Also, a cheap and easy solution, is to use a graphic query builder (like *shiver* MSAccess ;)... although, you should have one of those. Better than Access's even! Ah! I remember figuring out sums and stuff was as hard graphically... hrm... that sorta looks like generated stuff already eh. Maybe J has an idea, he's a SQL ninja. I'd start with a left join from the teacher table, and work my way out, since I want ALL instructors. Sorry no code 'zample )-; ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273062 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Need some SQL help!
Ok, I wrote down in plain english what I need. Show me a list of questions, all possible evaluation answers for each question, and a total of each answer chosen. Does this make sense? Thanks, Will ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273050 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Need some SQL help!
MySQL 5. I'm building a dynamic question survey form. Here's my db schema: http://wtomlinson.com/evalDBSchema.jpg I need to pull data for a specific instructor. How do I get a count for each answer? I need to display each question like this: http://wtomlinson.com/evaldisplay.jpg I'm workin on the query and having a hard time figuring out howta write it. This returns 3 records. I filled out a 3 question survey to get that. Now, I need to total up answers. Here's what I've got so far. Could someone point me in the right direction? SELECT tblevaluations.EvalID, tblevaluations.Course, tblevaluations.GtccDeptID, tblevaluations.InstructorID, tblevaluations.Program, tblevaluations.Section, tblevaluations.SectionID, tblevaluations.DivCode, tblevalanswerresults.evalID, tblevaluations.DeptPrograms, tblevalanswerresults.answerID, tblevalanswerresults.evalanswerID, tblevalanswerresults.questionID, tblevalanswerresults.questionsetID, tblquestions.answersetID, tblquestions.question, tblquestions.questionID, tblquestions.questionsetID, tblquestions.questionsortnum, tblanswersets.answersetid, tblanswersets.answersetname, SUM(tblanswers.answerID) AS numAnswers, tblanswers.answersetID, tblanswers.answername, tblanswers.answersortnum, tblanswers.answervalue, tblquestionsets.questionsetID, tblInstructor.instructorLastName, tblInstructor.instructorFirstName, tblInstructor.InstructorID FROM tblEvaluations, tblevalanswerresults, tblquestions, tblanswersets, tblInstructor, tblanswers , tblquestionsets WHERE tblevaluations.EvalID = tblevalanswerresults.evalID AND tblevalanswerresults.answerID = tblanswers.answerID AND tblevalanswerresults.questionID = tblquestions.questionID AND tblevalanswerresults.questionsetID = tblquestionsets.questionsetID AND tblanswers.answersetID = tblanswersets.answersetid AND tblquestions.questionID = tblevalanswerresults.questionID AND tblevaluations.InstructorID = '0103419' GROUP BY tblquestionsets.questionsetID, tblquestions.questionID, tblanswersets.answersetid, tblanswers.answerID Thanks, Will ~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:273049 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
