title: counting in joins
body: <p>I have many to many association between <code>contact</code> and <code>project</code>.</p>
  
  <pre><code>contact:
  +-----------------+--------------+------+-----+---------+----------------+
  | Field           | Type         | Null | Key | Default | Extra          |
  +-----------------+--------------+------+-----+---------+----------------+
  | id              | int(11)      | NO   | PRI | NULL    | auto_increment |
  | deleted         | boolean      | NO   |     | NULL    |                |
  +-----------------+--------------+------+-----+---------+----------------+
  
  project:
  +-----------------+--------------+------+-----+---------+----------------+
  | Field           | Type         | Null | Key | Default | Extra          |
  +-----------------+--------------+------+-----+---------+----------------+
  | id              | int(11)      | NO   | PRI | NULL    | auto_increment |
  | status          | varchar      | NO   |     | NULL    |                |
  +-----------------+--------------+------+-----+---------+----------------+
  
  project_contact:
  +---------------+---------+------+-----+---------+-------+
  | Field         | Type    | Null | Key | Default | Extra |
  +---------------+---------+------+-----+---------+-------+
  | project_id    | int(11) | NO   | PRI | NULL    |       |
  | contact_id    | int(11) | NO   | PRI | NULL    |       |
  | proj_con_role | varchar | NO   |     | NULL    |       |
  +---------------+---------+------+-----+---------+-------+
  </code></pre>
  
  <p>I would like to count how many contacts are associated with no projects, one project or more than one project. But, for those 2 latter (one project and more than one project) the project status has to be <code>'STATUS_X'.</code> The <code>proj_con_role</code> has to be <code>'CLIENT'</code> and also, the contact must not be marked as deleted. If I could get that in one single query, that'd be absolutely awesome, if not, 3 different queries would do as well.</p>
  
  <p>I have this so far:</p>
  
  <pre><code>SELECT   numprojects,
           Count(*) AS numcontacts
  FROM     (
                     SELECT    c.id,
                               Count(pc.contact_id) AS numprojects
                     FROM      contact c
                     LEFT JOIN project_contact pc
                     ON        pc.contact_id = c.id
                     AND       pc.proj_con_role = 'CLIENT'
                     WHERE     (
                                         c.deleted isnull
                               OR        c.deleted = false)
                     GROUP BY  c.id ) c
  GROUP BY numprojects
  ORDER BY numprojects
  </code></pre>
  
  <p>Now, this works fine, but for the life of me, I cannot seem to add the condition that the project has to have a certain status... I have no idea how to add it. Any help would be absolutely great.</p>
  
  <p>I have tried adding:</p>
  
  <pre><code>left join project p on p.status = 'STATUS_X' and p.id = pc.project_id
  </code></pre>
  
  <p>but of course, it doesn't work like this...</p>
  
  <p>later edit 1:</p>
  
  <p>if I add:</p>
  
  <pre><code>inner join project p on p.status = 'STATUS_X' and p.id = pc.project_id
  </code></pre>
  
  <p>I get the correct results for 1 or more projects, but the contacts on no projects are ignored. Maybe a union here? Not sure.</p>