SQL上的GROUP BY和JOIN问题

I have 2 tables one with Employee names and Employee Codes, the other with the same employee codes but with the employee's arrival and departure time. One column on the latter table contains a varchar that states whether they arrived late or early and the table shows values for 3 days and 6 employees. I need to use join to show the first and last name from the first table with every column from the second table and also not show employees that have arrived late one or more times. The broken code I have now is:

我有2个表,一个是员工姓名和员工代码,另一个是员工代码相同,但有员工的到达和离开时间。后一个表中的一列包含一个varchar,用于说明它们是迟到还是早到,并且该表显示了3天和6名员工的值。我需要使用join来显示第一个表中的第一个和最后一个名称以及第二个表中的每一列,并且也不显示已经迟到一次或多次的员工。我现在破碎的代码是:

SELECT 
    EmpRegister.LastName, 
    EmpRegister.FirstName, 
    TimeSheet.* 
FROM EmpRegister 
    INNER JOIN TimeSheet ON EmpRegister.ID=TimeSheet.EmpID 
WHERE Flag <> 'Late'
GROUP BY Emp.ID

1 个解决方案

#1

I don't have the time to make up a set of test data, but try this:

我没有时间编写一组测试数据,但试试这个:

SELECT DISTINCT
    EmpRegister.LastName, 
    EmpRegister.FirstName, 
    TimeSheet.* 
FROM EmpRegister 
INNER JOIN TimeSheet 
    ON EmpRegister.ID=TimeSheet.EmpID 
WHERE NOT EXISTS (
  SELECT 1
  FROM TimeSheet 
  WHERE  EmpRegister.ID = TimeSheet.EmpID
  AND Flag = 'Late'
  )