Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Posts
    93

    Unanswered: Awful SQL Statement

    Hi folks - can anyone help please.

    I have 3 tables listed below and can return successfully the number and names of employees that have not taken a course. My problem is that i would also like to have the course name included in the returned records but am unable to construct such a sql statement to achieve same. Any help is greatly appreciated or is it even possible

    Tables are like follows:

    Employees Table
    Employeeid
    Employee

    Emp_Records Table
    Recordsid
    Employeeid
    Courseid

    Course_Name Table
    Courseid
    CName

    I have got a sql statement working to find records of employees who have not done a course etc.

    SELECT DISTINCT TOP (100) PERCENT dbo.Employees.Employee
    FROM dbo.Employees LEFT OUTER JOIN
    dbo.Emp_Records ON dbo.Employees.EmployeeID = dbo.Emp_Records.EmployeeID
    WHERE (NOT EXISTS
    (SELECT Recordsid, EmployeeID, Courseid
    FROM dbo.Emp_Records AS Emp_Records_1
    WHERE (dbo.Employees.EmployeeID = EmployeeID) AND (Courseid = '1')))
    ORDER BY dbo.Employees.Employee

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you already know which course it is that you want the employee names and number who didn't take it

    e.g. you already know it's courseid=1

    so how come you don't know what the name of that course is?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2009
    Posts
    93
    Thanks for reply. Yes, i know the course name but when i click on the Cname to include in the statement, the sql designer creates a cross join and so returns the incorrect number of records

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here ya go...
    Code:
    SELECT dbo.Course_Name.CName
         , dbo.Employees.Employee
      FROM dbo.Course_Name
    CROSS
      JOIN dbo.Employees 
    LEFT OUTER 
      JOIN dbo.Emp_Records 
        ON dbo.Emp_Records.EmployeeID = dbo.Employees.EmployeeID
       AND dbo.Emp_Records.Courseid = dbo.Course_Name.Courseid
     WHERE dbo.Course_Name.Courseid = 1
       AND dbo.Emp_Records.EmployeeID IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •