title: mysql - multiple joins
body: <p>Given these tables defitinion and relationships :   </p>
  
  <pre><code>jobs  
  tasks  
  invoice_item  
  invoice_payment  
  </code></pre>
  
  <p>Jobs are the root of everything.<br>
  each job has multiple tasks, for example the job may be building a chair and tasks might be the steps required. Each task has a certain planned price, so buying wood for the chair can cost 400.</p>
  
  <p>each task is can be billed through an invoice, invoices are stored in two tables, one table holds the invoice_id(invoice_item) while the other holds the invoice payment details like the amount to bill and to which task to bill.  </p>
  
  <p>There can be multiple invoices per task, so for example our task that costs 400 can have 4 invoices of 100 each.    </p>
  
  <p>Requirements : </p>
  
  <p>Display each job in it's own row with the following information :<br>
   1. sum of planned price for the job<br>
   2. sum already billed for the job<br>
   3. the job's description</p>
  
  <p>I've created this sqlfiddle to provide basic data structure.
  <a href="http://sqlfiddle.com/#!2/0be74" rel="nofollow">http://sqlfiddle.com/#!2/0be74</a>
  The expected result for this data set is : </p>
  
  <pre><code>(job_description, SUM(planned), SUM(billed))  
  VALUES  
  ('Project A', 2000, 1900),    
  ('Project B', 3000, 2000) 
  </code></pre>
  
  <p>My current single and only solution is failing horribly :  </p>
  
  <pre><code>Select j.job_description, SUM(t.task_planned_price),SUM(p.invoice_amount)
  FROM jobs as j
  LEFT JOIN tasks as t ON t.job_id = j.job_id
  JOIN invoice_item as i ON i.task_id = t.task_id
  JOIN invoice_payment as p ON p.invoice_id = i.invoice_id
  GROUP BY j.job_id
  </code></pre>
  
  <p>I'll deeply appreciate any guidance and some hints (or answers) as to what I'm doing wrong.
  You can copy my solution directly to the sqlfiddle and see what breaks.</p>
  
  <p>Thanks in advance and sorry for the long question!</p>