如何对表示具有多个答案的民意调查的两个表使用一个查询？

Question

I have two tables poll and poll_answers, which represent a poll and the options to choose from to answer a question from the poll. EX:

Am I poor at SQL?

• yes
• yes, you do
• change craft

and the co-responding tables:

poll

pollID pollQuestion

 1 | Am I poor at SQL?

---

poll_answers

pollAnswerID pollAnswerText pollID

  1 | yes | 1

  2 | yes, you do | 1

  3 | change craft | 1

---

and this is how I get the data:

$polls=$db->get_results("SELECT pollID, pollQuestion FROM poll",ARRAY_A);
    foreach ($polls as $poll_field)
    {
        $poll['id']=$poll_field['pollID'];
        $poll['question']=$poll_field['pollQuestion'];
        $tmp=$poll['id'];
        //answers
        $answers=$db->get_results("SELECT pollAnswerID, pollAnswerText FROM poll_answers WHERE pollID='$tmp'",ARRAY_A);
            {
            //and so on , I think you get the idea.
            }


    }

It looks very clumsy to me as I think that it is possible to get the data with only one SQL query using INNER JOIN on the ID match...I just couldn't do it. Can you help? Keep in mind that there are multiple polls in my database.

---

Edit: thank you for the answers so far. I appreciate the help. But I didn't explain the question well. Is it possible to get all the polls with all the answers in an array or object using only one SELECT. In the answers so far you used the $tmp variable which is already taken from a previous query. So is it possible to do it or is it me not getting the answers?

Answer 1

SELECT pollQuestion, pollAnswerID, pollAnswerText
FROM   poll_answers pa, poll p
WHERE  p.pollID='$tmp'
       AND pa.pollId = p.pollID

or, if you prefer INNER JOIN syntax,

SELECT pollQuestion, pollAnswerID, pollAnswerText
FROM   poll p
INNER JOIN
       poll_answers pa
ON     pa.pollId = p.pollID
WHERE  p.pollID='$tmp'

To get eveything in an array, you use:

SELECT  -1, pollQuestion
FROM    poll p
WHERE   p.pollID = @pollID
UNION ALL
SELECT  pollAnswerID, pollAnswerText
FROM    poll_answers pa
WHERE   pa.pollID= @pollID