Show 0 values if they are present

Transact-SQL
1

HI There.

i want to pull 0 values however my query doesnt allow for 0 values and it takes long to execute.

Here is my code:

select a.Owner_Id,
b.Name as KPI,
B.Record_Type,
B.Resource_Id,
C.Display_Name,
count(*) as Max

from amgr_appointments A
right outer join AMGR_Resources B on A.Owner_Id = b.Resource_Id
right outer JOIN ADMN_User_Details AS C ON A.Creator_Id=C.User_Id

where b.Resource_Id in ('ROE534B758E', 'R0E42A431B5', 'R0E42A4BB3F','R0E42A3E514','R0E42A44D19', 'R0E42A37FBB')
and b.Record_Type = 3
and a.Creator_Id In('AMCKENZIE','ASARAK', 'JMALAN')
and A.App_Date between '2016-02-29'and '2016-03-29'
Group by a.Owner_Id, a.Creator_Id, b.Name, B.Record_Type, B.Resource_Id, c.Display_Name

I get the following results:

amckenzie 1 Pop-in
ASARAK 2 Pop-in

However i want this result that includes the user even if the value is 0

amckenzie 1 Pop-in
ASARAK 2 Pop-in
JMALAN 0 Pop-in

Please help me and show me where im going wrong. I have tried different joins however the query runs too long

2

You have an OUTER JOIN on [B] but you have tests on [B] in the WHERE clause, and that will force the JOIN on [B] to become an inner join.

Might that be the problem?

3

I can't get my head around your RIGHT Outer Joins ... but that's just me. LEFT Outer Join much more common ...

4

Not exactly sure which result column you intend to count, but maybe this will do it:

select a.Owner_Id,
b.Name as KPI,
B.Record_Type,
B.Resource_Id,
C.Display_Name,
sum(case when b.Record_Type is null then 0 else 1 end) as Max

from amgr_appointments A
right outer join AMGR_Resources B on A.Owner_Id = b.Resource_Id 
    and b.Resource_Id in ('ROE534B758E', 'R0E42A431B5', 'R0E42A4BB3F','R0E42A3E514','R0E42A44D19', 'R0E42A37FBB')
    and b.Record_Type = 3
right outer JOIN ADMN_User_Details AS C ON A.Creator_Id=C.User_Id

where a.Creator_Id In('AMCKENZIE','ASARAK', 'JMALAN')
and A.App_Date >= '20160229' and A.App_Date < '20160330'
Group by a.Owner_Id, a.Creator_Id, b.Name, B.Record_Type, B.Resource_Id, c.Display_Name
5

Right Outer Join is a bit harder to understand because we are used to seeing the preserved table on the left and non-preserved tables on the right. However, when using right outer join - the table on the far right is the preserved table.

Converting the above to left outer join we would get:

ADMN_User_Details LEFT OUTER JOIN AMGR_Resources LEFT OUTER JOIN AMGR_Appointments

With that said - using any columns from AMGR_Resources or AMGR_Appointments in the where clause will cause the results to act like an inner join and eliminate those rows from AMGR_User_Details where there are no related rows in the other tables.

If that is the expectation - then the query should be converted to use inner joins. If that is not the expectation - then we would need additional information to identify how to filter the data appropriately. One option would be to move all items in the where clause to the join clause - which will insure that all rows from AMGR_User_Details are returned.

6

That's how I read it (despite me saying it was doing my head in!!) and I drew the conclusion that it must be a mistake and that LEFT OUTER JOINs had been intended (i.e. [A] is required, [B] and [C] are optional) as I can't figure out how why there would be so many conditions on [A] if that table is optional.

So perhaps the issue here is that RIGHT has been used by accident instead of LEFT?

(Still needs [B] conditions to be in the JOIN to the [B] table, and not in the WHERE clause, but the [A] conditions could & should stay in the WHERE)