MYSQL: Complex Query Question

Question

Hello all,

I am working with polls: Each poll has many options and users can vote in polls once. Thus I have a 'votes' table with the following fields:

• id (id of the vote)
• option_id (id of the poll option chosen)
• user_id (id of the user)
• poll_id (id of the poll)

So here is what I'm trying to do: Given an array of poll_ids, I want to have a query return the most voted on option for each poll. Thus if I give poll_id's of 1 & 2, I want to get back the most voted on options for polls 1 & 2. I have tried the following:

SELECT
   t1.poll_id,
   t1.option_id,
   count(t1.option_id) AS num_votes,
   t2.option_id AS user_vote 
FROM 
   votes AS t1 
JOIN 
   votes AS t2 
ON 
   t1.id = t2.id
WHERE 
   t1.poll_id IN (30,40)
GROUP BY
   t1.option_id;

That almost does the trick...but gives me all poll options with corresponding votes for each poll provided, not just the most voted option. If anyone has any ideas, I'd really appreciate it. Thanks.

Answer 1

SELECT  (
        SELECT  option_id
        FROM    votes v
        WHERE   v.poll_id = p.id
        GROUP BY
                option_id
        ORDER BY
                COUNT(*) DESC
        LIMIT 1
        ) cnt
FROM    polls p
WHERE   p.id IN (1, 2)

Answer 2

I don't think you'll need a self join to solve this, but Quassnoi's answer seems correct.

Answer 3

Quassnoi's answer will do it. You don't need to join the table you just need to use a count(*) operation to return the sum and then order by that sum to see who does best. The GROUP BY tells the mysql engine what categories to count, in your case to count the different poll options.