OUTER JOINS? - 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

SQL Server Development (2000)

OUTER JOINS?

Author

Topic

Mannga
Yak Posting Veteran

70 Posts

Posted - 2003-11-25 : 08:49:05

Hi all,

I am trying to get a list of users with a count of how much work they have attached to them at the moment. The problem I am getting is when there is a new employee he has no work attached and is not being returned in the Results?

Here is what I have


Select  P.[User] as Employee, E.Department as Dept, Count(qryAllocUser) From ProActiveUser P
Join Employees E ON E.Logon_Name = P.[User]
Left Outer Join Queries Q ON Q.qryAllocUser  = P.[User]
Where E.Active = 1 AND qryNo LIKE 'CPR%' And Not Q.qryStatus in ('Resolved', 'Finalised') 
Group By P.[User], E.Department
Order by Count(qryAllocUser)

The users are stored in ProActiveUser and there work is stored in Queries. All the employees info is stored in Employees.

If I comment out the requirement to look in the queries table then I get a list of the employees and a count that is correct. THe problem is I need to get a list of the employees specific to these requirements?

Thanks,
Gavin

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-11-25 : 09:00:51

Try moving the criteria of the OUTER JOINED table into the FROM like so:

Select  P.[User] as Employee, E.Department as Dept, Count(qryAllocUser) From ProActiveUser P
Join Employees E ON E.Logon_Name = P.[User]
Left Outer Join Queries Q ON Q.qryAllocUser  = P.[User]
	AND qryNo LIKE 'CPR%' And Not Q.qryStatus in ('Resolved', 'Finalised') 
Where E.Active = 1 
Group By P.[User], E.Department
Order by Count(qryAllocUser)

Mannga
Yak Posting Veteran

70 Posts

Posted - 2003-11-25 : 09:32:57

You are the Master :)...

You have no idea the headache I had from thinking this one over... and it was such a simple answer..

Cheers...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources