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

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:

我有两个表poll和poll_answers,它们代表一个民意调查以及可供选择回答民意调查问题的选项。 EX:

Am I poor at SQL?

我在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.

它对我来说看起来非常笨拙,因为我认为在ID匹配时使用INNER JOIN只能使用一个SQL查询来获取数据...我就是做不到。你能帮我吗?请记住,我的数据库中有多个民意调查。

---

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?

编辑:谢谢你到目前为止的答案。我很感激帮助。但我没有很好地解释这个问题。是否可以使用一个SELECT获取数组或对象中所有答案的所有轮询。在答案到目前为止,您使用了$ tmp变量,该变量已从先前的查询中获取。那么有可能做到这一点还是我没有得到答案?

3 个解决方案

#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,

或者,如果您更喜欢INNER JOIN语法,

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:

要获取数组中的eveything,您可以使用:

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

#2

SELECT polls.pollID, answers.pollAnswerID, answers.pollAnswerText 
FROM poll polls
LEFT JOIN poll_anwers answers ON polls.pollID = answers.pollID
WHERE polls.pollID = " . (int) $pollId . "

#3

You want all answers for all polls? Just remove the pollID constraint:

你想要所有民意调查的所有答案吗?只需删除pollID约束:

SELECT p.pollID p.pollQuestion, pa.pollAnswerID, pa.pollAnswerText FROM poll p, poll_answers pa WHERE pa.pollID = p.pollID

SELECT p.pollID p.pollQuestion,pa.pollAnswerID,pa.pollAnswerText FROM poll p,poll_answers pa WHERE pa.pollID = p.pollID