Mysql on the left joins two tables

Question

Assuming I have 2 tables:

leave_type

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

employee_leave_blance

id      employee_id      year     leave_type_id     val
1       4                2015      1                9
2       4                2015      2                5
3       8                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 employee_id=4

In the table employee_leave_blancefor employee_id=4only 3 left_type record, but in a table leave_typeof 5. I want to get 5 entries for the employee. If the entry is not found in employee_leave_blance, it should return 0.

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

employee_id      name                val
4                maternity leave     9
4                Medical leave       5
4                Casual Leave        4
4                Sick Leave          0
4                honeymoon Leave     0

For this, I tried the following query:

select
    el.employee_id, lt.name, el.val  from leave_type as lt
    left join employee_leave_blance as el on el.leave_type_id = lt.id
where
    el.year = YEAR(CURDATE()) and el.employee_id = 4

However, it returns the following results:

employee_id      name                val
4                maternity leave     9
4                Medical leave       5
4                Casual Leave        4

Now, how can I get the values ​​for leave_type for an employee?

Answer 1

Not sure if this is what you need, try:

select ifnull(el.employee_id,4) as employee_id,
lt.name,ifnull(el.val,0) as val
from leave_type as lt
left join (select year,employee_id,val,leave_type_id 
                    from employee_leave_balance) as el 
                    on el.leave_type_id = lt.id 
                    and el.year = year(curdate()) 
                    and el.employee_id = 4

, . where and, ifnull 0 . :

select
    ifnull(el.employee_id,4) as employee_id, lt.name, ifnull(el.val,0) as val  
from leave_type as lt
    left join employee_leave_balance as el on el.leave_type_id = lt.id
and
    el.year = YEAR(CURDATE()) and el.employee_id = 4