title: How to use LIKE for mysql search with JOIN and ORDER BY the count of most rows/votes in the vote table?
body: <p>I have three tables I need to use in a search, Movies, Reviews, and Votes. I want to use the LIKE function for Movie.Title and Review.Subject and order them by the amount of the most votes for each match.</p>
  
  <p>In the Votes table there is a ReviewID, UserID, IsGood. Every time a user votes, an insert is done with the MovieID, UserID, and 1 or 0 for the IsGood, 1 meaning good 0 meaning bad.</p>
  
  <p>So one review may have 0 good and bad votes, or 5 good and 3 bad, etc. I would like to show the results in the following order:</p>
  
  <p><strong>Review 1 - 10Good / 3Bad</strong></p>
  
  <p><strong>Review 2 - 4Good / 3Bad</strong></p>
  
  <p><strong>Review 3 - 0Good / 0Bad</strong></p>
  
  <p>The matches with the most good votes at top, the ones with the most bad votes at the bottom.</p>
  
  <p>This is the mysql query I wrote up and is obviously wrong, but hoping someone can help me out:</p>
  
  <pre><code>mysql_query("
      SELECT m.Title, r.Subject, v.ReviewID FROM Movies m
          LEFT JOIN Reviews r
              ON m.ID=r.MovieID
          INNER JOIN Votes v
              ON r.ID=v.ReviewID
          WHERE (m.Title LIKE '%" . $search . "%'
              OR r.Subject LIKE '%" . $search . "%')
          ORDER BY MAX(COUNT(v.IsGood='1')) LIMIT 10")or die(mysql_error());
  </code></pre>