title: mysql left join with three table
body: <p>Assuming I have 3 tables:</p>
  
  <p><strong>leave_type:</strong></p>
  
  <pre><code>id   |    name
  1    |    maternity leave
  2    |    Medical leave
  3    |    Casual Leave
  4    |    Sick Leave
  5    |    honeymoon Leave
  </code></pre>
  
  <p><strong>employee</strong></p>
  
  <pre><code>id       |       name
  4        |       Employee 1
  7        |       Employee 2
  </code></pre>
  
  <p><strong>employee_leave_blance</strong></p>
  
  <pre><code>id   |   employee_id   |   year   |  leave_type_id  | val   
  1    |   4             |  2015    |    1            |  9    
  2    |   4             |  2015    |    2            |  5   
  3    |   7             |  2015    |    1            |  10   
  4    |   4             |  2015    |    3            |  4
  </code></pre>
  
  <p>Here, <code>employee_leave_blance.leave_type_id = leave_type.id</code></p>
  
  <p>Now I want to get all <code>Leave Type</code> values for all employee.</p>
  
  <p>Let me give an example: I want to get output for <code>employee_id = 4, 7</code></p>
  
  <pre><code>employee_id  |    name              |  val
  4            |    maternity leave   |  9
  4            |    Medical leave     |  5
  4            |    Casual Leave      |  4
  4            |    Sick Leave        |  0
  4            |    honeymoon Leave   |  0
  7            |    maternity leave   |  10
  7            |    Medical leave     |  0
  7            |    Casual Leave      |  0
  7            |    Sick Leave        |  0
  7            |    honeymoon Leave   |  0
  </code></pre>
  
  <p>My query</p>
  
  <pre><code>SELECT
      lt.name, ifnull(el.val,0) as val , el.employee_id 
  FROM leave_type AS lt
      LEFT JOIN employee_leave_balance AS el ON el.leave_type_id = lt.id
  AND
      el.year = YEAR(CURDATE()) GROUP BY el.employee_id
  </code></pre>