I posted this on the code help forum, but realize it would be better placed on this forum.
Ok, here's the set up, I have four tables: question, choices, answers, and answers_other that have the following fields:
question: id
choices: id | question_id | content
answers: response_id | question_id | choice_id
answers_other: response_id | question_id | choice_id | response
What I need to do is pull all the answers for a particular question contained in the "answers" and "answers_other "tables, and have them in one single array (preferably one mySQL query result). Basically I need the "choice_id" and the corresponding answer (which for the "answer" table is found in the "content" field of the "choices" table, and for "answers_other" is found in the "response" field). In the "choices" table, the "content" field for an other answer is defined as other, as the actual value is not set, but rather entered by the user.
Here is the query I have come up with thus far and it is close to working:
SELECT C.id,C.content
FROM answers A, question Q, choices C, answers_other O
WHERE A.response_id='$rid'
AND A.question_id=Q.id
AND A.choice_id=C.id GROUP BY C.id
ORDER BY C.id
The problem is that it does not pull the "response" from the "answers_other "table, but the "content" (which is "other" as opposed to the user entered answer).
I'm hoping that makes sense and someone can provide me with the correct query or solution that I need. I'm not too familiar with LEFT JOIN, but perhaps that is what I need to use to get the desired results. Thanks in advance!
James
