|
I have written following query for a stored procedure:
SELECT
M.ProgramCode,
M.MeetingCode,
CASE
WHEN MA.AttendeeType = 1 THEN 'Participant'
WHEN MA.AttendeeType = 2 THEN 'Speaker/Faculty'
END AS AttendeeType,
Count(A.AttendeeID) as NoofRSVP,
COUNT(CASE WHEN MA.Status=4 THEN 'present' END) as NoofAttendee
FROM
Programs P
INNER
JOIN eCDRReservations M
ON P.SubCompanyCode = M.SubCompanyCode
AND P.ProgramCode = M.ProgramCode
left outer
JOIN MeetingAttendees MA
ON M.ReservationID = MA.MeetingID
and MA.AttendeeType = 1
left outer
JOIN Attendees A
ON MA.AttendeeID = A.AttendeeID
and MA.AttendeeType = 1
left outer
JOIN Regions R
ON MA.RegionCode = R.RegionCode
WHERE
P.SubCompanyCode = @SubCompanyCode AND
P.ProgramCode = @ProgramCode AND
(MA.status = 2 OR MA.status = 4)
this query returns following values:
ProgramCode, MeetingCode, AttendeeType, NoofRSVP, NoofAttendee
In this query I have used MA.AttendeeType = 1 condition in my joins, 1 represent 'Participant' in the table, I am counting only 'Participants' here.
But it wont return any values if there is no Participant for some meeting, means Meeting is present in Programs and eCDRReservations table but there is no Attendeetype=1 in MeetingAttendees table for that meeting.
My task is, I have to modify the query in such a way so that it return 0 as NoofRSVP and 0 as NoofAttendee for all the meetings where there is no Participant. I have to keep all the where clauses too.
|
