title: Counting references in a self referenced table based on both the referring and referenced rows
body: <p>I am working on a table that stores two levels of tasks (let's call it <code>primary</code> and <code>secondary</code>), in the same table using self-referential foreign keys. Column <code>secondary_for</code> is a self-reference on <code>tasks.id</code> where as <code>user</code> is a reference to a different table.</p>
  
  <p>Each <code>primary</code> task will need 2 <code>secondary</code> tasks to be sourced from various users (one from each) other than the user who created the <code>primary</code> task for it to be "resolved". Given a user, I'm trying to find a way to rank the unresolved <code>primary</code> tasks for that user (so I can create a new <code>secondary</code> task against it) that enables the fastest completion of addressing the unresolved <code>primary</code> tasks.</p>
  
  <p>So, if the state of <code>tasks</code> table is as follows:</p>
  
  <pre><code>+----+------+---------------+-----------------------------+
  | id | user | secondary_for |            desc             |
  +----+------+---------------+-----------------------------+
  |  1 |    1 | NULL          | Primary Task 1              |
  |  2 |    2 | 1             | Update 1 for Primary Task 1 |
  |  3 |    3 | 1             | Update 2 for Primary Task 1 |
  |  4 |    2 | NULL          | Primary Task 2              |
  |  5 |    1 | 4             | Update 1 for Primary Task 2 |
  |  6 |    3 | NULL          | Primary Task 3              |
  +----+------+---------------+-----------------------------+ 
  </code></pre>
  
  <p>... and user 100 (who exists in <code>users</code> table), is available to add a secondary task, I want to present a list of eligible primary row ids sorted by the number of the secondary task against it currently:</p>
  
  <pre><code>+-----------+
  | id |  ctr |
  +-----------+
  |  4 |    1 |
  |  6 |    0 |
  +-----------+
  </code></pre>
  
  <p>Here rows 4 and 6 are presented because:</p>
  
  <ol>
  <li><p>they are primary tasks which user 100 hasn't created </p></li>
  <li><p>user 100 hasn't already created a secondary task against these tasks</p></li>
  <li><p>they don't have the required number of secondary tasks (ie. 2) yet,
  as opposed to row 1</p></li>
  </ol>
  
  <p>I'm new to MySQL so I'm not sure how to approach this, but here's what I've got so far:</p>
  
  <pre><code>SELECT secondary_for AS id, COUNT(id) AS ctr FROM tasks
  WHERE secondary_for IN
      (SELECT id FROM tasks WHERE secondary_for IS NULL AND user !=100)
      AND user != 100
  GROUP BY secondary_for
  HAVING COUNT(id) &lt; 2
  ORDER BY COUNT(id) DESC
  </code></pre>
  
  <p>which gets me upto:</p>
  
  <pre><code>+-----------+
  | id |  ctr |
  +-----------+
  |  4 |    1 |
  +-----------+
  </code></pre>
  
  <p>but does not show row 6, which is also eligible but is not retrieved as there are no secondary tasks assigned to it yet.</p>
  
  <p>How can I fix this? Also, I am almost certain that there is a more efficient way to construct this query using JOINs but don't know how to go about it.</p>
  
  <p>Any help would be highly appreciated. 
  Thanks in advance!</p>