我有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/

10-16 18:58
查看更多