How to use one query for two tables representing a poll with question and multiple answers?

Question

Hi, 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:

DO I suck at SQL?

• yes
• yes, you do
• change craft

and the co-responding tables:

poll

pollID pollQuestion

1 | DO I suck 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. Thank you.

---

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 guys use 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

Answer 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 . "

Answer 3

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

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