title: Aggregate function with many-to-many inner join
body: <p>I have a foo table which has a one-to-many relationship with the BAR table.
  FOO also has a many-to-many relationship with QUX.</p>
  
  <p>To illustrate:</p>
  
  <pre><code>FOO
  +--------+
  | fid    | 
  +--------+
  |    1   |
  |    2   |
  |    3   |
  |    4   |
  +--------+
  
  
  BAR
  +------+-------+-----+
  | bid  | cost  | fid |
  +------+-------+-----+
  |    1 |     2 |  1  |  
  |    2 |     4 |  1  |
  |    3 |     8 |  2  |
  |    4 |    42 |  3  |
  |    5 |    21 |  3  |
  |    6 |     5 |  4  |
  |    7 |    98 |  2  |
  |    8 |    30 |  4  |
  +------+-------+-----+
  
  FOOQUX
  +------+------+
  | fid  | qid  |
  +------+------+
  |    1 |    2 |
  |    2 |    1 |
  |    2 |    3 |
  |    4 |    1 |
  +------+------+
  
  QUX
  +------+
  | qid  |
  +------+
  |    1 |
  |    2 | 
  |    3 |
  |    4 |
  +------+
  </code></pre>
  
  <p>Now I want to calculate the sum of BAR.cost for every FOO entry, but I want to filter by QUX in terms of "FOO has an association with any element of QUX"</p>
  
  <p>I tried using this sentence:</p>
  
  <pre><code>SELECT fid, sum(cost)
  FROM FOO
  INNER JOIN BAR on BAR.fid = FOO.fid
  LEFT JOIN FOOQUX ON FOOQUX.fid = FOO.fid
  </code></pre>
  
  <p>But this returns an entry for each instance of FOO in FOOQUX, so in the case of fid=2 (which has two QUX associated to it) we'll have double the amount of cost than in reality.</p>
  
  <p>Maybe this is a simple problem but I'm struggling on how to solve it.</p>