SQL JOINS help please... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL JOINS help please...

Author

Topic

empyrean
Starting Member

14 Posts

Posted - 2009-12-29 : 22:20:52

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

raky
Aged Yak Warrior

767 Posts

Posted - 2009-12-30 : 01:39:25

[code]
Hi,

This is the solution

declare @Leader Table (ID varchar(10),EmployeeID varchar(30), LeaderID varchar(30))
insert into @Leader
select 'id1', 'Emp1', 'leader1' union all
select 'id2', 'Emp2','leader1' union all
select 'id3', 'Emp3', 'leader1' union all
select 'id4', 'Emp4', 'leader1' union all
select 'id5', 'Emp5', 'leader2' union all
select 'id6', 'Emp6', 'leader2'

--select * from @leader

declare @Employee Table ( EmployeeId varchar(10) ,ProjectID varchar(10), WorkingHours int)
insert into @Employee
select 'Emp1', 'Proj1', 110 union all
select 'Emp2', 'Proj1', 80 union all
select 'Emp2', 'Proj2', 10 union all
select 'Emp3', 'Proj2', 222

--select * from @Employee

declare @Project Table (ProjectID varchar(10), ProjectName varchar(64))
insert into @Project
select 'Proj1','Projectname1' union all
select 'Proj2', 'Projectname2' union all
select 'Proj3', 'Projectname3' union all
select 'Proj4', 'Projectname4'

declare @LeaderID varchar(30),@ProjectID varchar(30)
select @LeaderID = 'leader1' ,@ProjectID = 'Proj1'

Select a.LeaderID, a.EmployeeID, b.WorkingHours, c.ProjectID
from @Leader a
left outer join @Employee b on b.EmployeeID = a.EmployeeID and b.ProjectID = @ProjectID
left join @Project c on b.ProjectID = c.ProjectID and c.ProjectID = @ProjectID
where LeaderID = @LeaderID
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources