Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2009
    Posts
    1

    Unanswered: Sql Joins Help Please

    I have three tables Leader, Employee and Project

    Leader Table
    ID EmployeeID LeaderID
    id1 Emp1 leader1
    id2 Emp2 leader1
    id3 Emp3 leader1
    id4 Emp4 leader1
    id5 Emp5 leader2
    id6 Emp6 leader2

    Employee Table
    EmployeeId ProjectID WorkingHours
    Emp1 Proj1 110
    Emp2 Proj1 80
    Emp2 Proj2 10
    Emp3 Proj2 222

    Project Table
    ProjectID ProjectName
    Proj1 Projectname1
    Proj2 Projectname2
    Proj3 Projectname3
    Proj4 Projectname4


    Select a.LeaderID, a.EmployeeID, b.WorkingHours, c.ProjectID
    from LeaderTable a left outer join EmployeeTable b
    on b.EmployeeID = a.EmployeeID left join ProjectTable c
    on b.ProjectID = c.ProjectID
    where LeaderID = @LeaderID and c.ProjectID = @ProjectID


    When i use this query i am getting the values like the following result

    Parameters LeaderID = Leader1 and ProjectID = Proj1

    LeaderID EmployeeID WorkingHours ProjectID
    Leader1 Employee1 110 Proj1
    Leader1 Employee2 80 Proj1

    But i want the result to include null values and all the employees under leader should be displayed but only working hours

    with respect to specific project should change. If an employee under leader is not involved in project, it should be

    displayed with null values in working hours. It should look like following for same parameters as above

    LeaderID EmployeeID WorkingHours ProjectID
    Leader1 Employee1 110 Proj1
    Leader1 Employee2 80 Proj1
    Leader1 Employee3 Null Proj1
    Leader1 Employee4 Null Proj1

  2. #2
    Join Date
    Jun 2008
    Location
    Chennai
    Posts
    21
    When you use the Where clause filter c.ProjectID = @ProjectID, the result set is eliminating the Emp3 and Emp4 values from Employee table.

    Try the following code

    Select a.LeaderID, a.EmployeeID, b.WorkingHours, a.projectID

    From
    (Select L.LeaderID, L.EmployeeID, P.ProjectID
    From Leader L, Project P) as a

    Left Outer Join Employee b
    On a.EmployeeID = b.EmployeeID

    Where a.LeaderID = @LeaderID and a.ProjectID = @ProjectID

  3. #3
    Join Date
    Jun 2008
    Location
    Chennai
    Posts
    21
    And if you have huge amount of data then my query will result in degrade of query execution performance

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Consider moving some of your WHERE clause to the ON clause instead of a derived table join.
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    doesn't need a derived table

    also, doesn't need the projects table
    Code:
    SELECT a.LeaderID
         , a.EmployeeID
         , b.WorkingHours
      FROM LeaderTable AS a 
    LEFT OUTER
      JOIN EmployeeTable AS b
        ON b.EmployeeID = a.EmployeeID 
       AND b.ProjectID = @ProjectID  
     WHERE a.LeaderID = @LeaderID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2008
    Posts
    135
    Code:
    SELECT a.LeaderID, a.EmployeeID, b.WorkingHours, c.ProjectID
    FROM Leader a 
    LEFT JOIN Employee b ON b.EmployeeID = a.EmployeeID AND b.ProjectID = @ProjectID
    LEFT JOIN Project c ON b.ProjectID = c.ProjectID 
    WHERE LeaderID = @LeaderID

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bklr View Post
    Code:
    SELECT a.LeaderID, a.EmployeeID, b.WorkingHours, c.ProjectID
    FROM Leader a 
    LEFT JOIN Employee b ON b.EmployeeID = a.EmployeeID AND b.ProjectID = @ProjectID
    LEFT JOIN Project c ON b.ProjectID = c.ProjectID 
    WHERE LeaderID = @LeaderID
    why did you include the project table?

    i guess you ignored my previous post, eh
    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
  •