title: SQL Combining Counts with Joins
body: <p>I have three tables:</p>
  
  <pre><code>Messages
  messageid | userid | text 
  Ex: 1 | 1303 | hey guys
  
  Users
  userid | username
  Ex:
  1303 | trantor
  1301 | tranro1
  1302 | trantor2
  
  Favorites
  messageid | userid
  Ex:
  1 | 1302
  1 | 1301
  </code></pre>
  
  <p>What I want to do, is display a table that has usernames, and counts the number of times they're messages were favorited a certain number of times. In the example above, I want to query saying "how many messages does each user have that has been liked exactly twice?"
  and it would show a table that has a row saying </p>
  
  <pre><code>trantor | 1
  </code></pre>
  
  <p>A natural extension is to replace exactly twice with "at least 2", "more than 6", etc. Im trying to combine count with joins and find myself confused. And since the tables are large, Im getting counts but not confident that my query is working correctly. I have read <a href="http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/" rel="nofollow noreferrer">this article</a> but am still confused :L</p>
  
  <p>What I have so far: </p>
  
  <pre><code>SELECT USERS.username, COUNT(FAVORITES.id) FROM USERS INNER JOIN FAVORITES ON FAVORITES.userID=USERS.id WHERE COUNT(FAVORITES.id) &gt; 2;
  </code></pre>
  
  <p>But I dont think it works.</p>
  
  <p>On S.O. I've found <a href="https://stackoverflow.com/questions/16525279/combining-sql-join-with-count">these</a><a href="https://stackoverflow.com/questions/598355/mysql-statement-combining-a-join-and-a-count"> questions</a> on "correlated subqueries" but am thoroughly confused.</p>
  
  <p>Would it be something like this?</p>
  
  <pre><code>SELECT  USERS.username,
   ,      (       SELECT  COUNT(FAVORTIES.userid)
                  FROM    FAVORITES INNER JOIN ON MESSAGES
                  WHERE   FAVORITES.messageid = MESSAGES.messageid
          )
  FROM    USERS 
  </code></pre>