我有3张桌子,是员工,配额和请假。
我希望查询显示雇员表中的所有记录。
3个表的主键作为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
--------------------------------------------
我需要显示所有带病假的员工记录,无论是否请假,如果没有请病假,请在桌子上显示,里面没有记录。因此,我只能获得员工请病假的记录,而且我还需要累计员工请假的天数。
请帮助,我是第一次在Stack Overflow中发帖。
谢谢。
最佳答案
$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'";
要么
这将给你想要的输出
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'
关于php - Mysql连接3个表并输出第一个表中的所有记录,而不管另外2个表记录是否为空,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/17851447/