CODEWITHSUNDEEP
phpmysql
Iqbal Hossen
Iqbal Hossen

Reputation: 113

mysql left join with three table

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

Upvotes: 3

Views: 92

Answers (3)

Mohib
Mohib

Reputation: 174

i have update my 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)

Sample on SQL Fiddle

run this query.i haven't tried it but i hope it will work 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

Upvotes: -1

Mohit maru
Mohit maru

Reputation: 827

Try this code 

SELECT * FROM leave_type 
INNER JOIN employee_leave_blance on leave_type.id = employee_leave_blance.leave_type_id 
INNER JOIN `employee on employee.id = employee_leave_blance.employee_id

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18767

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.

Upvotes: 2

Related Questions