I am stuck with a complex MySQL query. I have following tables:
我遇到了复杂的MySQL查询。我有以下表格:
[employee]
id
name
[department]
id
name
[employee_department_relation]
id
employee_id
department_id
[attendance]
id
employee_id
date
Note: attendance will be recorded only if the employee is present at that date. No specific flag is set for absentism.
注意:仅当员工在该日期在场时才会记录出勤率。没有特定的标志为缺席。
I need to find out that, how many employees from which department were absent on a particular date.
我需要知道,在特定日期,有多少员工缺席。
I have done so far, following SQL, but, its not perfect:
到目前为止,我已经完成了SQL,但是,它并不完美:
SELECT A.date,
(SELECT name FROM employee WHERE id = emp_id) AS employee,
(SELECT D.name AS dept_name
FROM deptartment D
INNER JOIN employee_department_relation R ON R.dept_id = D.id
INNER JOIN employee E ON E.id = R.emp_id
WHERE R.emp_id = A.emp_id
) AS dept
FROM attendance A
WHERE A.date = '2014-07-03'
Please let me know if this question makes any sense.
如果这个问题有意义,请告诉我。
1 个解决方案
#1
3
try this changing date as you like:
请根据需要尝试更改日期:
select employee.name, department.name from employee
left join employee_department_relation r on r.employee_id = employee.id
left join department on department.id = r.department_id
where employee.id not in (
select employee_id from attendance where date = '2014-07-28'
)
#1
3
try this changing date as you like:
请根据需要尝试更改日期:
select employee.name, department.name from employee
left join employee_department_relation r on r.employee_id = employee.id
left join department on department.id = r.department_id
where employee.id not in (
select employee_id from attendance where date = '2014-07-28'
)