Mysql on the left is merged with three tables
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 values Leave Type
for all employees.
Let me give you an example: I want to get the 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 request
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
+3
source to share
3 answers
You can do it like this:
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 .
+2
source to share
I have an updated answer
Update:
select lt.name,e.id,elb.val from
leave_type as lt, employee as e,employee_leave_blance as elb
where e.id = elb.employee_id and lb.leave_type_id = lt.id
and elb.year= YEAR(CURDATE()) order by (e.id)
SQL Fiddle Example
run this query. I haven't tried it, but hope it works fine
select * from leave_type as lt, employee as e, employee_leave_balance as elb
where e.id = elb.employee_id and elb.leave_type_id = lt.id and e.id = $id
-1
source to share