title: SQL Multiple COUNT() from two tables, within a LEFT JOIN
body: <p>i have three tables, i want to select all data from one of the tables, and also collect a COUNT of how many times that specific row of data is linked in the other two tables.</p>
  
  <p>So, SELECT all data from site_projects. Then return a COUNT of site_project_members WHERE <code>site_projects</code>.<code>id</code> = <code>site_project_members</code>.<code>pid</code> and also return a COUNT of site_project_tasks WHERE <code>site_projects</code>.<code>id</code> = <code>site_project_members</code>.<code>pid</code></p>
  
  <p>I hope i'm making sense, to the query, it looks correct. and it querys the database (MySQL) with no problems. <strong>Except</strong> it returns a sum of both of the counts as both things. (see below the table structures)</p>
  
  <p>site_projects</p>
  
  <pre><code>id | title     | desc      | start      | deadline   | progress
  
  1  | Project 1 | a project | 1321748906 | 1329847200 | 20
  </code></pre>
  
  <p>site_project_members</p>
  
  <pre><code>id | pid | uid | img | hidden
  1  | 1   | 1   | 1   | 0
  2  | 1   | 2   | 2   | 0
  </code></pre>
  
  <p>site_project_tasks</p>
  
  <pre><code>id | pid | desc   | completed
  1  | 1   | Task 1 | 1
  1  | 1   | Task 2 | 0
  </code></pre>
  
  <p>Here is my query:</p>
  
  <pre><code>SELECT p.`id`, p.`title`, p.`desc`, p.`progress`, p.`start`, p.`deadline`, COUNT(m.`id`) as `members`, COUNT(t.`id`) as `tasks` FROM `site_projects` p LEFT JOIN `site_project_members` m ON p.`id`=m.`pid` LEFT OUTER JOIN `site_project_tasks` t ON p.`id`=t.`pid` ORDER BY p.`id` ASC
  </code></pre>
  
  <p>The result i get is:</p>
  
  <pre><code>id | title     | desc      | progress | start      | deadline   | members | tasks
  1  | Project 1 | a project | 20       | 1321748906 | 1329847200 | 4       | 4
  </code></pre>
  
  <p>Both of the "4" values should be 2. however they are not :S Anyone able to help?
  Much Appreciated</p>
  
  <p>Thanks,
  Dan</p>