title: MySQL left join with multiple tables and where clauses
body: <p>I have tried so many combinations so maybe I'm just simply doing it wrong. Well, I definitely am doing it wrong.</p>
  
  <p>I know that code is always requested on Stack Overflow but it'll confuse the question as my code has now mutated into another language.</p>
  
  <p><strong>Confusion:</strong></p>
  
  <p>There's two <code>WHERE</code> clauses and no matter where I place it, I either get an error or the <code>JOIN</code> doesn't work</p>
  
  <p><strong>Problem:</strong></p>
  
  <p>I have 4 tables:</p>
  
  <p><code>customers</code> - I need ALL the rows returned even if there's no payment to it but only if <code>customer_status = 1</code></p>
  
  <p><code>payments</code> - contains <code>customer_id</code> linked to <code>customers</code> table - I need the SUM returned from <code>payment_amount</code> only if <code>payment_status = 1</code> </p>
  
  <p><code>branches</code> - contains <code>customer_id</code> linked to <code>customers</code> table</p>
  
  <p><code>managers</code> - contains <code>branch_id</code> linked to <code>branches</code> table</p>
  
  <pre><code>CUSTOMER    |    TOTAL_RECEIVED    |    BRANCH      |    MANAGER_NAME
  ----------------------------------------------------------------------
  Pepsi       |    £1000             |    London      |    Mr Smith
  Coca Cola   |                      |    Manchester  |    Mr Beckham
  Dr Pepper   |    £2500             |    Shanghai    |    Mr Miagi
  Duff        |                      |    Springfield |    Mr Simpson
  </code></pre>
  
  <p>As you can see, coca cola and duff haven't paid (no record in payment table) but it should still list</p>
  
  <ul>
  <li>So customers should all show up if <code>customer_status = 1</code></li>
  <li><code>SUM</code> of payment only if it's in database and payment_status = 1</li>
  <li>Branch and Manager should list for that customer as it's always in database</li>
  <li>Managers is in its own table as there's many and assigned to different branches at different times (as I have a feeling someone might ask)</li>
  </ul>
  
  <p><strong>Fields selected and where summary</strong></p>
  
  <pre><code>c.customer_id, c.customer_companyname, c.customer_status FROM customers c WHERE c.customer_status = 1
  
  SUM(p.payment_amount) as total_received, p.customer_id, p.payment_status FROM payments p WHERE p.payment_status = 1
  
  b.branch_id, b.branch, b.customer_id FROM branches b WHERE b.customer_id = c.customer_id
  
  m.manager_id, m.manager_name, m.branch_id FROM managers m WHERE m.branch_id = b.branch_id
  </code></pre>