I've changed the INNER JOIN that you mentioned to having two seperate INNER JOIN's instead of the subquery.
So far same result (that is good though, at least it didn't mess anything up).
Thank you.
I do not know of a way to NOT use distinct.
This is what I am trying to accomplish.
For any client that has one or more engagements that meet a criteria, count one for them.
So let's say I have 100 engagements and 40 of them meet the criteria. I don't want it to return a count of 40, I want it to return the number of Clients. Thus if I do distinct client_id, it should return (for instance) 5.
When i run the subquery:
CODE
select Distinct E.Client_ID
from Engagements E
WHERE E.Reports_Out is NULL and E.Classification = 'Verification (Ongoing)' AND E.Inactive IS NULL
That works great and returns the number I expect.
That query within my main one (now looks like this):
CODE
Select
EmpMe.Status as MyStatus,
EmpMe.Full_Name as TheName,
NULL as Total_Clients,
count(Active_O.Client_ID) as Active_Ongoing,
count(InActive_O.Client_ID) as Inactive_Ongoing,
NULL as Total_Ongoing,
NULL as Active_Retros,
NULL as Inactive_Retros,
NULL as Total_Retros
FROM Engagements as E
INNER JOIN Clients as C on C.ID = E.Client_ID
INNER JOIN Rep_Client as RC on RC.Client_ID = C.ID and RC.Client_ID = E.Client_ID
INNER JOIN Employees as EmpMe on EmpMe.ID = RC.Emp_ID
LEFT JOIN (
select Distinct E.Client_ID
from Engagements E
WHERE E.Reports_Out is NULL and E.Classification = 'Verification (Ongoing)' AND E.Inactive IS NULL
) As Active_O on C.ID = Active_O.Client_ID and RC.Client_ID = Active_O.Client_ID and E.Client_ID = Active_O.Client_ID
LEFT JOIN (
select Distinct E.Client_ID
from Engagements E
WHERE E.Reports_Out is NULL and E.Classification = 'Verification (Ongoing)' AND E.Inactive IS NOT NULL
) As InActive_O on C.ID = InActive_O.Client_ID
WHERE EmpMe.Status = 'Partner' and RC.Eng_ID is Null
Group By EmpMe.Status, EmpMe.Full_Name
Order By TheName
That returns the number of engagements. I've seen this happen before and so far it's always been with a goof in the join (missing a "something.id = somethingelse.id") But as you may notice I've thrown in a bunch of extra of those, and I'm still getting the same improper result...
| 