mysql: How to INNER JOIN a table but limit join to 1 result with the highest vote or count?

Question

I have 2 tables. One is items and another is votes for those items.

Items table has: |item_id|name|post_date
Votes table has: |votes_id|item_id|answer|total_yes|total_no

What I want to do is show all items based on post_date and show the answer in the votes table with the HIGHEST total_yes. So I want to show only a SINGLE answer from the votes table with the highest total_yes vote.

I was trying:

SELECT a.*, b.* FROM Items a
INNER JOIN Votes b ON a.item_id = b.item_id
GROUP by a.item_id
ORDER by a.post_date DESC, b.total_yes DESC

But that doesnt work.

The result I would like to see is:

<item><answer><yes votes>
Buick | Fastest | 2 yes votes
Mercedes | Shiny | 32 yes votes
Honda | Quick | 39 yes votes

Any help is appreciated!

Answer 1

add LIMIT 1 to the end of your query :)

that will take only one record, but at the moment you're ordering by the date first, so you'll get the highest vote for the last date voted on. Is that what you want?

If you want the highest total vote regardless you'll need to order by that first.

Answer 2

SELECT a.*, b.*
  FROM Items a
       LEFT JOIN Votes b on a.item_id = b.item_id
                         and b.total_yes = (select max(total_yes) 
                                              from Votes v 
                         where v.item_id = a.item_id)
ORDER BY a.post_date DESC, b.total_yes DESC

N.B.: if you have for an item 2 answers with the same total_yes = max, you will have 2 rows for that item.