title: MySql 3 tables, nested joins
body: <pre><code>Invoice
  |    id   | invoice_date|
     123435   2016/02/09
     123436   2016/02/09
     123437   2016/02/09
  
  Payments
  |invoice_id| pmt_type| amount |
     123435       1       1000
     123435       2       2500
     123436       2       5000
     ...
  
  Payments_legend
  |type| description| 
     1      cash       
     2      check 
     3      credit 
  </code></pre>
  
  <p>I'm in over my head with this one.  I need to pull daily transactions and thought it would be simple.  The problem I'm running into is a number of nested joins, unless there is a simpler way to do this.  On the surface, it really does seem simple.</p>
  
  <p>The following gives me all the invoices I need...</p>
  
  <pre><code>SELECT id AS Invoice FROM invoice WHERE DATE(invoice_date) = DATE(NOW())
  </code></pre>
  
  <p>But that's where I get confused.  I need the sum for each payments.amount column grouped by invoice and separated into columns for each payments.pmt_type.  Yikes.</p>
  
  <p>I need the report to be as follows...</p>
  
  <pre><code>id  sum(pmt_type=1)  sum(pmt_type=2)  sum(pmt_type=3)
  </code></pre>
  
  <p>It would look like...</p>
  
  <pre><code>Invoice  Cash  Check  Credit
  123435   1000  2500   0
  123436   0     5000   0
  123437   0     0      7500
  </code></pre>
  
  <p>Any help would be greatly appreciated.</p>
  
  <p><strong>ADDED THANKS TO NECULON'S HELP</strong></p>
  
  <pre><code>SELECT invoice.id,
  sum(if(payments.pmt_type = 1, payments.amount, 0)) AS Cash,
  sum(if(payments.pmt_type = 2, payments.amount, 0)) AS Chek,
  sum(if(payments.pmt_type = 3, payments.amount, 0)) AS Credit,
  sum(if(payments.pmt_type = 9, payments.amount, 0)) AS Warranty,
  sum(if(payments.pmt_type = 10, payments.amount, 0)) AS Paypal,
  sum(if(payments.pmt_type = 7, payments.amount, 0)) AS Refund,
  (sum(if(payments.pmt_type = 1, payments.amount, 0))+sum(if(payments.pmt_type = 2, payments.amount, 0))+sum(if(payments.pmt_type = 3, payments.amount, 0))+sum(if(payments.pmt_type = 9, payments.amount, 0))+sum(if(payments.pmt_type = 10, payments.amount, 0))-sum(if(payments.pmt_type = 7, payments.amount, 0))) AS Total
  FROM invoice
  JOIN payments ON invoice.id = payments.invoice_id
  WHERE DATE(invoice_date) = DATE(now())
  GROUP BY invoice.id WITH ROLLUP
  </code></pre>
  
  <p>That gives me what I need.  Thanks again!</p>