title: MYSQL: Complex Query Question
body: <p>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:</p>
  
  <ul>
  <li>id (id of the vote) </li>
  <li>option_id (id of the poll option chosen)</li>
  <li>user_id (id of the user)</li>
  <li>poll_id (id of the poll)</li>
  </ul>
  
  <p><strong>So here is what I'm trying to do</strong>: 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 &amp; 2, I want to get back the most voted on options for polls 1 &amp; 2.  I have tried the following:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>That almost does the trick...but gives me <em>all</em> 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.</p>