mysql左边连接三个表

Question

Assuming I have 3 tables:

leave_type:

id   |    name
1    |    maternity leave
2    |    Medical leave
3    |    Casual Leave
4    |    Sick Leave
5    |    honeymoon Leave

employee

id       |       name
4        |       Employee 1
7        |       Employee 2

employee_leave_blance

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

Here, employee_leave_blance.leave_type_id = leave_type.id

Now I want to get all Leave Type values for all employee.

Let me give an example: I want to get output for employee_id = 4, 7

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

My query

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

Answer 1

You can do it this way:

SELECT employee_id,leave_name,IFNULL(val,0) as val
FROM

(SELECT e.id as employee_id,l.id as lid,l.name as leave_name
 FROM employee e CROSS JOIN leave_type l) as T1 LEFT JOIN

(SELECT val,employee_id as eid,leave_type_id as lid
 FROM employee_leave_blance elb) as T2 ON T1.employee_id=T2.eid AND T1.lid=T2.lid

ORDER BY employee_id asc,val desc

Result:

employee_id leave_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           Casual Leave     0
7           honeymoon Leave  0
7           Sick Leave       0
7           Medical leave    0

Sample result in SQL Fiddle.