CODEWITHSUNDEEP
mysqlcoldfusion
Suleman khan
Suleman khan

Reputation: 1068

rows are multiplied because of joining table?

I am querying "questions" and joining it with "answers". Each question may have two or more answers. Due to which, the number of questions returned are being multiplied by the number of answers. This prevents me from implementing pagination. How can I avoid this? Thanks.

Here is my query:

<cfquery name="questions">
    SELECT
       questions.id as questionid,
       questions.question as question,
       questions.rank as rank,
       questions.isrequired as isrequired,
       questiontypes.id AS questiontypeid,
       questiontypes.name as questiontype,
       questiontypes.template as template,
       questions.survey_id as survey_id,
       surveys.name as surveyname,
       surveys.questionsperpage as questionsperpage,
       surveys.thankyoumsg as thankyoumsg,
       answers.id as answerid,
       answers.answer as answer
    FROM
       questions
   LEFT JOIN answers ON answers.question_id = questions.id
   INNER JOIN questiontypes ON questions.questiontype_id = questiontypes.id
   INNER JOIN surveys ON questions.survey_id = surveys.id
   WHERE
   questions.survey_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.surveyid#">  
</cfquery>

Upvotes: 0

Views: 169

Answers (3)

Leigh
Leigh

Reputation: 28873

This is more of a clarification, but as I mentioned in the comments, the alternatives suggested by Steve and Brad differ from the "list approach" in that they require two queries: one to retrieve the id's of the questions to display on the current page, and one to grab the associated answers.

There are different ways to implement it, but one option is using a "grouped" output as Steve mentioned. Say you are displaying 10 records per page and want to display results 11 through 20. First run a query to grab the question id's. 

    <!---  For clarity these are descriptive, rather than actual, column names --->
    <cfquery name="getCurrentPageQuestions" .....>
        SELECT  QuestionID
        FROM    Questions
        WHERE   SurveyID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.surveyid#"> 
        ORDER BY WhateverColumnsYouWant
        LIMIT  11, 20
    </cfquery>

Next, use those id's to filter your original JOIN. You can skip the filter on survey id because the first query took care of that.

Important: You MUST sort the results by the grouped column first. Otherwise, the grouped cfoutput will not work.

    <cfquery name="getAnswers" .....>
        SELECT
                q.QuestionID
                , q.Question
                , a.AnswerID
                , a.Answer
                , .....
        FROM    questions q
                    LEFT JOIN answers a ON a.questionID = q.questionID
                    INNER JOIN questiontypes qt ON qt.questionTypeID = q.questionTypeID
                    INNER JOIN surveys s ON s.surveyID = q.surveyID
        WHERE  q.questionID IN 
               (
                   <cfqueryparam value="#valueList(getCurrentPageQuestions.questionID)#" 
                        list="true" 
                        cfsqltype="cf_sql_integer">
               )
        ORDER BY q.questionID
    </cfquery>

Finally, use Steve's example to display the results in the proper format:

    <cfouput query="getAnswers" group="questionID">
         <!--- display question once --->
        #question#<br>

       <cfoutput> 
            <!--- display all answers --->
             - #answer#<br>
        </cfoutput>
    </cfoutput>

Upvotes: 2

Steve
Steve

Reputation: 64

Since this is tagged ColdFusion. I would like to propose a ColdFusion graceful solution, that is easier to deal with that comma joined lists.

if you use the <cfoutput> tag, with attribute query and group You can output your data hierarchically with 1 question and x answers.

<cfouput query="questions" group="questionID">
    #question#<br>
    <cfoutput>
    - #answer#<br>
    </cfoutput>
</cfoutput>

You can style this output in divs, or table rows, with a question / answer class that indents them below each other appropriately.

As to the side issue of "prevents me from implementing pagination" There are too many ways to skin that cat. I would suggest researching some ajax. Paginating your questions, and fetching the answers when a user clicks "Show answers"

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can get the ids and answers in comma-delimited lists using group_concat():

SELECT
       questions.id as questionid,
       questions.question as question,
       questions.rank as rank,
       questions.isrequired as isrequired,
       questiontypes.id AS questiontypeid,
       questiontypes.name as questiontype,
       questiontypes.template as template,
       questions.survey_id as survey_id,
       surveys.name as surveyname,
       surveys.questionsperpage as questionsperpage,
       surveys.thankyoumsg as thankyoumsg,
       group_concat(answers.id) as answerids,
       group_concat(answers.answer) as answers
    FROM
       questions
   LEFT JOIN answers ON answers.question_id = questions.id
   INNER JOIN questiontypes ON questions.questiontype_id = questiontypes.id
   INNER JOIN surveys ON questions.survey_id = surveys.id
   WHERE
   questions.survey_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.surveyid#">
   group by questions.id

Upvotes: 2

Related Questions