title: Circular join (or nested join) between 3 tables
body: <p>I have three tables, <code>rents[id, contract_id, paid]</code>, <code>contracts[id, active, unit_id]</code> and <code>units[id]</code>.<br>
  What I need is a query that will select all the rents that are paid AND that their contract is active, and to join the unit to the record.</p>
  
  <p>What I have tried is:</p>
  
  <pre><code>SELECT * FROM `rents` AS Rent
  LEFT JOIN units AS Unit
      INNER JOIN contracts AS Contract
      ON (Unit.id = Contract.unit_id AND Contract.active=true)
  ON Rent.unit_id = Unit.id 
  WHERE Rent.paid = true
  </code></pre>
  
  <p>The returned rows were close, I received only the paid rents, with the unit - however, it ignored the contract.active condition.</p>
  
  <p>Can you help please?</p>