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

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?

From stackoverflow

• 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
  

  chosta : Looks like rocket science but it works. I should take a look at some advanced Sql tutorials. ty

  chosta : @Quassnoi: can you please refer me to a page where these actions are explained more thoroughly (SELECT -1 and the use of the @ sign).ty

  Quassnoi : SELECT -1 just selects a constant `-1` instead of a column. @ defines a session variable: http://dev.mysql.com/doc/refman/5.0/en/using-system-variables.html

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

• 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