Post reply

How can I query multiple entries for false value

  • June 12, 2006 at 4:44 am

    #172901

    I have 3 tables, 1 containing employee details ordered by Employee number, 1 containing training courses, including course reference numbers and course descriptions, and the last table is a record of each employees training, which contains Employee number and course reference number. This last table can have multiple records for each Employee number, representing when a person has done more than one course. I can easily sort out those employees who have attended a particular course, but I am having alot of trouble trying to list staff who have not completed a particular course, due to the multiple records for each employee, testing for a course number not equal to a particular value still returns those people who have done the course, because they have other entries that satisfy the criteria. Any suggests or help would be greatly appreciated

  • June 12, 2006 at 6:58 am

    #643059

    How about

    select e.<FieldList> from Employees e

    where e.EmployeeId not in

    (select EmployeeId from EmployeeTraining where CourseRefNo = <CourseId&gt

    that should do the trick.

     

    S

  • June 12, 2006 at 7:00 am

    #643060

    SELECT Emp.EmployeeNumber

    FROM tblEmployees Emp

    LEFT JOIN

    (SELECT EmployeeNumber FROM tblEmployeeCourse

    WHERE CourseNumber = 'XXX') EC

    ON Emp.EmployeeNumber = EC.EmployeeNumber

    WHERE EC.EmployeeNumber IS NULL

  • June 13, 2006 at 4:23 am

    #643314

    Thanks for the prompt replies, which have enabled me to get the result I was after. They are also much simpler than the code I was trying to write, which is always a good thing.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply