Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2010
    Posts
    3

    Unanswered: SQL Newbie Needs Help

    Hi I am just starting to learn sql and need help with two queries. I am using Three Tables - Jobs,Department and Employee... Jobs Table has JobClass,Job. Department has DepartmentCode, Department and ManagerID. Employee has EmployeeNumber, EmployeeName,DepartmentCode,JobClass,Salary, HireDate


    PROBLEM 1
    Show each Department. Display the department code, the name of the department, the manager's name and the manager's title. Order by the name of the department. Two of the Departments have NULL for Manager ID and one Manager manages two departments. I am able to only retrieve 6 of the 9 departments and am struggling to retrieve the two departments with NULL value for Manager ID and the one manager that manages to departments:

    Here is my statement thus far:
    SELECT d.Department, d.DepartmentCode, e.FirstName + e.LastName AS Manager, e.Title
    FROM dbo.Department d
    left outer JOIN dbo.Employee e ON d.DepartmentCode = e.DepartmentCode
    WHERE d.ManagerNumber = e.EmployeeNumber
    ORDER BY Department;


    PROBLEM 2(Uses the same tables)
    For each Department, display the department code, the name of the department, the manager's name, the manager's title plus each employee and employee's title. Order by the name of the department and then by the employee's last name. I need help in retriveing the manager's name and title and associating it with the employee.

    Here is the statement that I have so far:
    SELECT e.FirstName, e.MiddleInitial, e.LastName,e.Title,,e.DepartmentCode,d.Department
    FROM dbo.Employee e
    left outer join dbo.Department d
    ON e.DepartmentCode = d.DepartmentCode
    ORDER BY Department,LastName;


    Any help provided is greatly appreciated!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when a LEFT OUTER JOIN is executed, rows from the left table are returned whether or not they have a matching row in the right table, and if there is no match, then the columns in the result row which would've come from the right table are filled with NULLs instead

    however, if the WHERE clause then requires that a column from the right table actually has to have a value -- and this is what your WHERE clause in problem 1 does -- then the unmatched rows are filtered out, and the overall effect is the same as if it were an inner join instead of a left outer join

    in problem 2 you will need two joins, one for the manager and another for the employees

    also, you cannot have two consecutive columns in the SELECT clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2010
    Posts
    3
    Thanks!! I removed the WHERE Clause and used a right outer join as follows:
    SELECT d.DepartmentCode,d.Department, e.FirstName + e.LastName AS Manager, e.Title
    FROM dbo.Employee e RIGHT OUTER JOIN dbo.Department d
    ON e.DepartmentCode = d.DepartmentCode
    AND d.ManagerNumber = e.EmployeeNumber OR d.ManagerNumber = e.EmployeeNumber
    ORDER By Department;

    Thanks again for your help!!

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    That may not always give you what you want. Might want to think a bit harder on AND/OR conditions and how they relate. In fact, you would see it better by switching those last two around.
    Dave

  5. #5
    Join Date
    Jul 2010
    Posts
    3
    Thanks Dave.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, there could be performance implications with the AND/OR clause. You might consider 2 representations of the table as Rudy suggested, one for employees and one for managers.
    Dave

Posting Permissions

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