JOIN problem - 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

SQL Server 2000 Forums

Transact-SQL (2000)

JOIN problem

Author

Topic

fm88
Starting Member

18 Posts

Posted - 2008-07-21 : 10:17:37

I have 3 tables employees,projectEst,and ProjectAct

employees with column EmployeeId
ProjectEst with column EmployeeId and ProjectId(and hours worked column)
ProjectAct with column EmployeeId and ProjectId(and hours worked column)

I want a query that displays all the employees and sum of the hours worked who worked on a given project...

my query is not displaying the employees in the projectAct table..

SELECT Name,Team,SUM(pe.EstPlanProg),SUM(pa.ActPlanProg),SUM(pe.EstBase),SUM(pa.ActBase),SUM(pe.EstSiteAssembly),
SUM(pa.ActSiteAssembly),SUM(pe.EstBuildAssembly),SUM(pa.ActBuildAssembly),SUM(pe.EstLightElec),
SUM(pa.ActLightElec),SUM(pe.EstLandScape),SUM(pa.ActLandScape),SUM(pe.EstFinalTouch),SUM(pa.ActFinalTouch),
SUM(pe.EstBoxShip),SUM(pa.ActBoxShip),SUM(pe.EstOther),SUM(pa.ActOther)
FROM dbo.tblEmployees em
LEFT JOIN dbo.tblProjEmpEst pe
ON em.EmployeeId=pe.EmployeeId
LEFT JOIN dbo.tblProjEmpAct pa
ON em.EmployeeId=pa.EmployeeId AND pa.UniqueProjId=pe.UniqueProjId
WHERE pe.UniqueProjId='1'
GROUP BY Name,Team

can anyone help...?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 10:25:28

Will you be having records in ProjectEst for all projects that an employee work on?

fm88
Starting Member

18 Posts

Posted - 2008-07-21 : 10:28:02

yes..i will have records with employee id and project id in projectest and projectact table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 10:45:31

quote:
Originally posted by fm88

yes..i will have records with employee id and project id in projectest and projectact table

What should be your output columns? Can you give some sample data?

fm88
Starting Member

18 Posts

Posted - 2008-07-21 : 11:12:59

output data should be like

emplid projiD hoursEst hoursAct
1 1 5 10
2 1 6 0
....

this means that the employee 1 has worked for project 1 estimated 5 hours,actual 10 hours
employee 2 has worked6 estimated hours,but not actual

in my query im able to get the employees from estimated tabel but not actual..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 11:21:49

quote:
Originally posted by fm88

output data should be like

emplid projiD hoursEst hoursAct
1 1 5 10
2 1 6 0
....

this means that the employee 1 has worked for project 1 estimated 5 hours,actual 10 hours
employee 2 has worked6 estimated hours,but not actual

in my query im able to get the employees from estimated tabel but not actual..

i cant see any problem with your query above. it will be more helpful if you can post some data from tables.

fm88
Starting Member

18 Posts

Posted - 2008-07-21 : 11:40:33

sample data from tables are

employee
employeeid name team
1 x team1
2 y team2

project est
projectid employeeid hoursest
1 1 6
2 5 10

projectact
projectid employeeid hoursact
1 1 6
1 1 10
1 2 7
etc...
(projectact table has also date column so u can have 2 records with same employee on different dates..itslike a timesheet..)

output is

for projectid=1

emplname team projid hoursest hoursact
x team1 1 6 16
y team2 1 0 7

thankss

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 11:50:42

[code]SELECT e.EmployeeId,pe.hoursest,pa.totalhours
FROM Employees e
LEFT JOIN [project est] pe
ON pe.EmployeeId=e.Employeeid
LEFT JOIN (SELECT projectid ,employeeid,sum(hoursact)as totalhours from projectact
group by projectid ,employeeid) pa
on (pa.projectid=pe.projectid or pe.projectid is null)
and pa.employeeid=e.employeeid
where (pe.projectid='1' or pe.projectid is null)
and (pa.projectid='1' or pa.projectid is null)[/code]

fm88
Starting Member

18 Posts

Posted - 2008-07-22 : 01:32:02

thanks visakh,its working fine,but can i do the same query and not show the employees that dont have hours in est or act?cause your solution is showing all the employees..
thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources