CODEWITHSUNDEEP
phpmysql
user2617464
user2617464

Reputation: 3

Mysql Join 3 tables and output all record in first table regardless another 2 table record is null

I have 3 tables which is employee,quota and leave. I want my query to show all records in employee table. 3 tables primary key as ID

employee
----------------
emp ID|name
----------------
1     |emp 1
2     |emp 2
3     |emp 3
4     |emp 4
----------------

quota
-----------------------
emp ID|leave type|quota
-----------------------
1     |annual    |5
1     |sick      |14
2     |annual    |4
2     |sick      |14
3     |annual    |3
3     |sick      |14
4     |annual    |2
4     |sick      |14
----------------------

leave
---------------------------------
emp ID|leave type  |date     |day
---------------------------------
1     |annual      |22-5-2013|0.5
2     |sick        |22-5-2013|1.0
1     |sick        |24-5-2013|1.0
1     |sick        |25-5-2013|1.0
---------------------------------

My expected result:
--------------------------------------------
emp ID|name |leave type|quota|date     |day
--------------------------------------------
1     |emp 1|sick      |14   |24-5-2013|2.0
2     |emp 2|sick      |14   |22-5-2013|1.0
3     |emp 3|sick      |14   |null     |null
4     |emp 4|sick      |14   |null     |null
--------------------------------------------

I need to show all employee record with sick leave whether have taken or not, and leave table if no taken sick leave, there have no record inside. So i only can get the record that employee have taken sick leave, and i also need total up how many days the employee total taken the leave.

please help, i'm first time post in Stack Overflow. Thanks.

Upvotes: 0

Views: 176

Answers (1)

Ranjan
Ranjan

Reputation: 263

$query="select emp.empId, emp.name, q.leavetype, q.quota, l.date, l.day from employee as emp inner join quota as q on emp.empId = q.empId and q.leavetype = 'sick' inner join leave as l on l.empId = emp.empId and l.leavetype = 'sick'";

or

this one will give your desire output

select e.id, e.name, q.leavetype, q.quota, le.date, le.day from emp as e inner join quota as q on e.id = q.empId left join emp_leave as le on e.id = le.empId and le.leavetype='sick'

Upvotes: 1

Related Questions