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)