query problem --with a view

I have a View which does what I need it too-

select distinct d.deptid, d.[description], d.supervisor, d.approvedby, case
when c1.cnt = c2.cnt then 'reviews are complete' else 'reviews still in progress' end as deptstatus
From department d
Left join (Select Count(*) cnt, deptid from employee group by deptid) c1 on (c1.deptid = d.deptid)
Left join (Select Count(*) cnt, deptid from employeeeval join employee
on (employeeeval.employeeid = employee.employeeid) group by deptid) c2 on (c2.deptid = d.deptid)

Brings back data as follows:

Cast - Cast - 00999 - 09246 - reviews still in progress
CMP- Copper Metal Products - 03315 - 09246 - reviews are complete
CNS- Copper Narrow Strip - 07530 - 09246 - reviews still in progress
CRW- Copper Rod and Wire - 01950 - 09246 - reviews still in progress

So I did the following:

select e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName,
CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status, d.deptstatus
from vw_DeptStatus d Left OUTER JOIN Employee e ON e.deptid = d.deptid LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u
ON u.Department = e.DeptID
WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') -- and (ev.PersonalScore is null)
ORDER BY e.DeptID, e.LastName

Now this isn't bringing back what I want--If run the query w/out joining it up w/the view it brings back the following:

Cast - Atkins, Carl - 09935 - Chris Burke - Not Started
Cast - Bridges, James - 09929 - Chris Burke - In Progress
CNS - Cunningham, Kenton - 02100 - Kahle Rummer - Not Started
CNS - Mitchell, Bill - 06200 - Kahle Rummer - In Progress

Now what I really need it to do is (below are the results from my View joined in with my query) -- return all the dept's--regardless if they are finished. If they are complete I only need ONE row, not all the rows for that dept. (highlighted in RED) I added these rows in-- Doesn't have to be NULL but maybe a blank string.

Cast - Charlton, Maurice - 01313 - Chris Burke - In Progress - reviews still in progress
Cast - Dorsey, Steve - 02455 - Chris Burke - In Progress - reviews still in progress
CMP - NULL - NULL - Fred Grove - NULL - reviews are complete
CNS - Bennett, Mark - 09911 - Kahle Rummer - In Progress - reviews still in progress
CNS - Buckingham, Mark - 00964 - Kahle Rummer - In Progress - reviews still in progress
CRW - Eubanks, Kellie - 07599 - Rick Cramer - In Progress - reviews still in progress
CRW - Luikart, Tyler- 09908 - Rick Cramer - In Progress - reviews still in progress
MicroMll - NULL - NULL - Tim Cross - NULL - reviews are complete

I hope this makes sense to someone -- if you have any questions just ask me. Another note I need to bring this back in a DS --for Crystal Reports. So the outline looks like this:

Dept ID- Supervisor - Reviews still in Progress
Any unfinished Reviews for that dept

Dept ID- Supervisor- Reviews Complete
No data shown since COMPELTE

Dept ID- Supervisor - Reviews still in Progress
Any unfinished Reviews for that dept

still not resolved -- just wondering if anyone had any ideas.|||

I just had the same problem. I am also having a problem with a left outer join. Reading your post led me to try testing with and without a view. I created two simple tables and did the LO join. That worked fine. Creating a select * view based on the first table then using that view in the LO join as the left table gave the erroneous result again. It is something to do with the fact that there is a view in there. I'll post again if I solve it.

Regards,

Raymond.

|||

Got it,

I stuck this in the top of the code for the view:

SELECT TOP 100 PERCENT *

This was instead of SELECT *. Replacing SELECT * with select colA, colB also worked.

I got the idea from the MS site:http://support.microsoft.com/kb/321541

Below is a snip of some of the text from the site. Putting DISTINCT in the select clause did not work BTW. Didn't try GROUP BY.

Cheers,

Raymond.

------snip-----

You can reproduce the behavior when all the following conditions exist:
? You use a query that contains an outer join, and you use a view (directly or indirectly) on the inner side of the outer join.
? The view contains an expression in its SELECT list that references one or more base table columns. Also, if all base table column values are NULL, the expression returns non-NULL results.
? The view is a simple view that does not contain a DISTINCT, TOP, or GROUP BY aggregate.

------snip-----

sql