title: Inner join with count() on three tables
body: <p>Simple and fast question, i have those tables:</p>
  
  <pre><code>//table people
  | pe_id | pe_name |
  | 1  | Foo  |
  | 2  | Bar  |
  //orders table
  | ord_id | pe_id | ord_title   |
  |   1    |   1   | First order |
  |   2    |   2   | Order two   |
  |   3    |   2   | Third order |
  //items table
  | item_id | ord_id | pe_id | title  |
  |   1     |   1    |   1   | Apple  |
  |   2     |   1    |   1   | Pear   |
  |   3     |   2    |   2   | Apple  |
  |   4     |   3    |   2   | Orange |
  |   5     |   3    |   2   | Coke   |
  |   6     |   3    |   2   | Cake   |
  </code></pre>
  
  <p>I need to have a query listing all the people, counting the number of orders and the <strong>total</strong> number of items, like that:</p>
  
  <pre><code>| pe_name | num_orders | num_items |
  | Foo  |    1       |   2       |
  | Bar  |    2       |   4       |
  </code></pre>
  
  <p>But i can not make it work!
  I tried </p>
  
  <pre><code>SELECT
      people.pe_name,
      COUNT(orders.ord_id) AS num_orders,
      COUNT(items.item_id) AS num_items
  FROM
      people
      INNER JOIN orders ON (orders.pe_id = people.pe_id)
      INNER JOIN items ON items.pe_id = people.pe_id
  GROUP BY
      people.pe_id;
  </code></pre>
  
  <p>But this returns the <code>num_*</code> values incorrect:</p>
  
  <pre><code>| name | num_orders | num_items |
  | Foo  |    2       |   2       |
  | Bar  |    8       |   8       |
  </code></pre>
  
  <p>I noticed that if i try to join one table at time, it works:</p>
  
  <pre><code>SELECT
      people.pe_name,
      COUNT(orders.ord_id) AS num_orders
  FROM
      people
      INNER JOIN orders ON (orders.pe_id = people.pe_id)
  GROUP BY
      people.pe_id;
  
  //give me:
  | pe_name | num_orders |
  | Foo     |          1 |
  | Bar     |          2 |
  
  //and:
  SELECT
      people.pe_name,
      COUNT(items.item_id) AS num_items
  FROM
      people
      INNER JOIN items ON (items.pe_id = people.pe_id)
  GROUP BY
      people.pe_id;
  //output:
  | pe_name | num_items |
  | Foo     |         2 |
  | Bar     |         4 |
  </code></pre>
  
  <p>How to combine those two queries in one?</p>