The 3rd and 4th clauses seem to be identical in your Where Clause
AND tblquestionsanswers_x.answersetid = tblanswersets.answersetid
AND tblanswersets.answersetid = tblanswersetsanswers_x.answersetid
That may be why you are getting a Cartesian Product.
Another thing to consider is the order that you list the tables in your From
clause, as opposed to the order they appear in your Where clause.
For example, in your where clause, you reference the table
tblquestionsanswers_x before tblanswersets. So you might consider placing it
before tblanswersets in the FROM clause.
I'm not sure if you are using Oracle or SQL Server.
Try using parentheses around the where conditions that depend on each other.
Also try using the INNER JOIN syntax instead and see what results you get (if
you are on SQL Server).
Hope any of those help.
A DBA once told me that when doing SQL Joins, after 3 tables the data is less
reliable. So the fact that you are joining on more than 3 tables will
introduce a whole new set of problems.
good luck,
Ali
> I usually don't have problems with grouping and outputting data, but
> this one's got my number so far...
>
> With my db schema, a question can be related to more than one answer
> set. An answer set can be related to more than one question.
>
> An answer set can be related to more than one answer. An answer can be
> related to more than one answer set.
>
> All my questions work except the rankings. I'm trying to do this.
>
> Question: Rank your favorite colors.
>
> Answer set: Blue Answers: <select options blah blah>1,2,3,4</select>
> Answer set: Green Answers: <select options blah blah>1,2,3,4</select>
> etc.. etc..
>
> Here's my query:
>
> SELECT
> tblquestionsets.questionsetid,
> tblquestionsets.questionsetname,
> tblquestions.questionid,
> tblquestions.questionsetid,
> tblquestions.question,
> tblquestions.questioncatid,
> tblquestions.questionsortnum,
> tblquestions.questionrequired,
> tblquestioncats.questioncatid,
> tblquestioncats.questioncatname,
> tblquestionsanswers_x.questionanswersetid,
> tblquestionsanswers_x.answersetid,
> tblquestionsanswers_x.questionid,
> tblanswersets.answersetid,
> tblanswersets.answersetname,
> tblanswersets.answersettypeid,
> tblanswersets.answersetrequired,
> tblanswersettypes.answersettypeid,
> tblanswersettypes.answersettypename,
> tblanswersetsanswers_x.answersetsanswersid,
> tblanswersetsanswers_x.answersetid,
> tblanswersetsanswers_x.answerid,
> tblanswers.answerid,
> tblanswers.answername,
> tblanswers.answervalue,
> tblanswers.answersortnum,
> tblquestionsets.questionsetdateadded
> FROM
> tblquestionsets,
> tblquestions,
> tblanswersets,
> tblquestionsanswers_x,
> tblanswersettypes,
> tblanswers,
> tblanswersetsanswers_x,
> tblquestioncats
> WHERE
> tblquestionsets.questionsetID = tblquestions.questionsetID
> AND tblquestions.questionid = tblquestionsanswers_x.questionid
> AND tblquestionsanswers_x.answersetid = tblanswersets.answersetid
> AND tblanswersets.answersetid = tblanswersetsanswers_x.answersetid
> AND tblanswersetsanswers_x.answerid = tblanswers.answerid
> AND tblanswersets.answersettypeid = tblanswersettypes.answersettypeid
> AND tblquestions.questioncatid = tblquestioncats.questioncatid
> AND tblquestionsets.questionsetID = 1
> AND tblquestions.questioncatid = 3
> ORDER BY tblanswersets.answersetid, tblanswers.answerid
>
> Here's my output:
>
> <cfoutput query="getAdditionalQuestions" group="questionID">
> <cfoutput>
> #answersetname#<br />
>
> <cfselect name="Q_question#ctr#">
> <option value="#questionID#_#answerID#">#answername#</option>
> </cfselect>
>
> </cfoutput>
> </cfoutput>
>
> But here's what it outputs:
>
> http://wtomlinson.com/questionCap.jpg
>
> I need it to show just one answer set, but with the multiple answers
> in the dropdown. I've tried playing with all sorts of combinations,
> with no luck.
>
> Any ideas?
>
> 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:274859
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
