mysql JOIN not select the answers if there is no votes
see this code
SELECT x.*, z.rates
FROM poll_answers x
JOIN
(SELECT answer_id, COUNT开发者_StackOverflow中文版( id ) AS `rates`
FROM poll_votes
GROUP BY answer_id) z
ON
x.id = z.answer_id
WHERE x.poll_id = '$poll_id'
ORDER BY x.id DESC
it just show the answer is it has more then 0 votes, this mean if my poll is new and there is not votes it will never show the answers
so the question how to update this code to show the answers if has no vots too
You must do a LEFT JOIN
instead of just a JOIN
:
SELECT x.*, z.rates
FROM poll_answers x
LEFT JOIN
(SELECT answer_id, COUNT( id ) AS `rates`
FROM poll_votes
GROUP BY answer_id) z
ON
x.id = z.answer_id
WHERE x.poll_id = '$poll_id'
ORDER BY x.id DESC
JOIN
or INNER JOIN
is used to join two tables and keeps only the rows which are present on both side.
LEFT JOIN
keeps all rows on the left table (the table in the FROM
clause) and add informations from the right table (the table you're joining with) when possible
RIGHT JOIN
keeps all the rows from the right table and add informations from the left table when possible.
In your case, since you want all the poll_answers
you must use LEFT JOIN
.
Be aware that rates
will be null
instead of 0 when there's no vote. You can replace the first line with this SELECT x.*, CASE z.rates IS NULL 0 ELSE z.rates
if you want to have 0.
SELECT x.*, z.rates
FROM poll_answers x
LEFT JOIN
(SELECT answer_id, COUNT( id ) AS `rates`
FROM poll_votes
GROUP BY answer_id) z
ON
x.id = z.answer_id
WHERE x.poll_id = '$poll_id'
ORDER BY x.id DESC
Try This
SELECT x . * , z.rates
FROM poll_answers x
LEFT JOIN (SELECT answer_id, COUNT( id ) AS `rates`
FROM poll_votes
GROUP BY answer_id) z
ON x.id = z.answer_id
WHERE x.poll_id = '$poll_id' ORDER BY x.id DESC
Additional to the answer posted, if you are filtering for a single query, you should - for performance reasons - add the poll_answers
to the grouping sub query, since you only want to count the votes for a single poll, not the entire table.
The inner query should look like this:
(SELECT v.answer_id, COUNT( v.id ) AS `rates`
FROM poll_answers a JOIN poll_votes v
ON a.answer_id = v.answer_id
WHERE a.poll_id = '$poll_id'
GROUP BY v.answer_id)
精彩评论