chosta chosta - 1 year ago 52
SQL Question

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

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:


pollID pollQuestion

1 | Am I poor at SQL?


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)
$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
. In the answers so far you used the
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 Source
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
       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
SELECT  pollAnswerID, pollAnswerText
FROM    poll_answers pa
WHERE   pa.pollID= @pollID