How to write this mysql query?
I have three tables tb_poll,tb_poll_answer,tb_po开发者_如何学Pythonll_votes
tb_poll has id,question
tb_poll_answer has id,poll_id,answer
tb_poll_votes has id,poll_answer_id
I want to select all questions and it answers(with count votes).
Finally i want to list
Id Question Answers
1 Which is....? 1.PHP(70) 2.ASP(30) 3.JSP(2)
2 ...... .......
How to write best mysql query here?
Try this query now:
SELECT q.id,q.question,
GROUP_CONCAT(CONCAT(a.id,'.',a.answer,COUNT(v.id) SEPARATOR ' ')) AS `Answers`
FROM `tb_poll` AS q
LEFT JOIN `tb_poll_answers` AS a
ON q.id = a.poll_id
LEFT JOIN `tb_poll_votes` AS v
ON a.id = v.poll_answer_id
WHERE 1
GROUP BY q.id
ORDER BY q.id
Finally i got result through sub query
SELECT q.id,q.question,av.vt,GROUP_CONCAT(CONCAT(av.answer,av.vt) SEPARATOR ' ')
AS Answers FROM tb_poll AS q LEFT JOIN
(SELECT a.answer,a.poll_id,COUNT(v.id) AS vt FROM tb_poll_answers AS a LEFT JOIN tb_poll_votes AS v ON a.id=v.poll_answer_id GROUP BY a.id) AS av
ON q.id=av.poll_id
GROUP BY q.id
精彩评论