Complex (ish) SQL join and count query

Question

Hi, I'm trying to create a simple poll function using php and sql. I have three tables:

Questions

Which simply contains each question asked

question_id | question_text | created_at

Answers

Which contains each answer for each question

question_id | answer_id | answer_text

Answered Questions

Which records who has voted for each option

question_id | answer_id | user_ip

I'm trying to write a query which will return a single question (the most recent) along with all the possible answers to that question and finally a count of each answer to each question. I know I will have to use a GROUP BY clause and possible LEFT OUTER JOIN, but the exact syntax is eluding me atm.

Any advice would be greatly appreciated. Thanks.

Answer 1

Assuming you're usnig MySQL:

SELECT  q.* ,
        (
        SELECT  COUNT(*)
        FROM    answered_questions aq
        WHERE   aq.answer_id = a.answer_id
                AND aq.question_id = q.question_id
        ) AS votes
FROM    (
        SELECT  *
        FROM    question
        ORDER BY
                created_at DESC
        LIMIT 1
        ) q
LEFT OUTER JOIN
        answers a
ON      a.question_id = q.question_id

Answer 2

SELECT
    questions.question_id,
    questions.question_text,
    answers.answer_id,
    answers.answer_text,
    COUNT(answered_questions.user_ip)
FROM
    questions,answers,
    answered_questions
WHERE
    questions.question_id=answers.question_id
    AND
    questions.question_id=
        (SELECT
            question_id
            FROM questions
            ORDER BY questions.created_at
            LIMIT 1
        )
    AND
    answered_questions.question_id=questions.question_id
GROUP BY
    questions.question_id

should work (although I haven't tested it).

Answer 3

This query should work on most DBMSs:

select q.question_id, question_text, a.answer_id, a.answer_text, count(user_ip)
  from questions q
 inner join answers a on (q.question_id = a.question_id)
  left join answered_questions aq on (a.question_id = aq.question_id
                                      and a.answer_id = aq.answer_id)
 where created_at = (select max(created_at)
                       from questions
                     )
 group by q.question_id, a.answer_id, q.question_text, a.answer_text

Answer 4

This is very similar to the logic in this article http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/.

Essentially you need a subquery which selects the single record / question you are interested in, as well as an outer query to select the information related to that record that you are interested in

(I could post another SQL statement to add to the nice collection that have already been posted, but I thought I'd try and shed some light onto how the other posted queries work)