title: Fetch row where COUNT() in another table and ID doesnt already exist
body: <p>I have 2 tables:</p>
  
  <pre><code>current_challenges (challenge_id, award_type, award_at_count, expires_in_hours)
  </code></pre>
  
  <p>and</p>
  
  <pre><code>user_challenges (user_challenge_id, user_id, challenge_id, awarded)
  </code></pre>
  
  <p>The <code>current_challenges</code> table is the type of challenge, and <code>user_challenges</code> table is currently 'active' and already 'completed' challenges for the different users. The tables are connected via the <code>challenge_id</code>. A completed challenge is one with <code>awarded</code> != '0000-00-00 00:00:00', and 'active' one is where <code>awarded</code> is a set date time.</p>
  
  <p>What I'd like to do is fetch a random single <code>challenge_id</code> that has not already been completed by that specific user, but if there is already 2 of that <code>award_type</code> active for the user, then it should not be selected.</p>
  
  <p>So there should only ever be a maximum of 2 challenges 'active with the same award_type for each user.</p>
  
  <p>Example:</p>
  
  <p>current_challenges table:</p>
  
  <pre><code>challenge_id    award_type  award_at_count  expires_in_hours
  49  1   1   24
  50  1   2   24
  51  1   3   24
  52  2   4   24
  53  2   5   24
  54  2   6   24
  </code></pre>
  
  <p>user_challenges table:</p>
  
  <pre><code>user_challenge_id   user_id     challenge_id    awarded
  1   8   49  0000-00-00 00:00:00
  2   8   50  0000-00-00 00:00:00
  3   8   52  2012-12-06 13:58:27
  4   11  53  0000-00-00 00:00:00
  5   11  54  0000-00-00 00:00:00
  </code></pre>
  
  <p>For user 8, <code>challenge_id</code> 49,50 wouldn't be selected because they are already active. 51 wouldn't because there are already 2 active with <code>award_type</code> = '1'. 52 wouldn't because it has already been completed, leaving either 53 or 54 as the returned <code>challenge_id</code>.</p>
  
  <p>Sorry for the long post, but wanted to get it as clear as possible. I've had a play for the past day, but got nowhere... <code>LEFT JOIN</code> and <code>HAVING COUNT()</code> somewhere I'm guessing, but i can't figure it out...</p>