Question regarding a Sql Query

Hello,

i have this query:
declare @.SubCompanyCode VARCHAR(6)
declare @.ProgramCode VARCHAR(10)
declare @.AttendeeTypeOption INT
DECLARE @.AttendeeTypeOptionOP CHAR(2)

set @.SubCompanyCode= 'A011'
set @.ProgramCode = 'ACL52B'
set @.AttendeeTypeOption= ''

If (@.AttendeeTypeOption<>'') SELECT @.AttendeeTypeOptionOP = 'EQ' ELSE SELECT @.AttendeeTypeOptionOP = ''

SELECT

M.City AS MeetingCity,
M.State AS MeetingState,
CASE
WHEN MA.AttendeeType = 1 THEN 'Participants'
WHEN MA.AttendeeType = 2 THEN 'Speaker/Faculty'

END AS AttendeeType,
Count(A.AttendeeID) as NoofRSVP
FROM
Programs P
INNER
JOIN eCDReservations M
ON P.SubCompanyCode = M.SubCompanyCode
AND P.ProgramCode = M.ProgramCode
left outer
JOIN MeetingAttendees MA
ON M.ReservationID = MA.MeetingID
left outer
JOIN Attendees A
ON MA.AttendeeID = A.AttendeeID
left outer
JOIN Regions R
ON MA.RegionCode = R.RegionCode
WHERE
P.SubCompanyCode = @.SubCompanyCode AND
P.ProgramCode = @.ProgramCode AND

CASE @.AttendeeTypeOptionOP
WHEN '' THEN 1
WHEN 'EQ' THEN
CASE

WHEN MA.AttendeeType = 1 THEN 1

ELSE 0
END
END=1
GROUP BY

M.City,
M.State,
MA.AttendeeType
ORDER BY
MA.AttendeeType

this query returns all 4 rows even though there is no matching meetingID in MeetingAttendees table. I have to modify this query (or write another query) in such a away that if I run it for "Participants" only means MA.AttendeeType = 1 (I will pass @.AttendeeTypeOption=1 for this case) then NoofRSVP should show #of participants and if MA.AttendeeType <> 1 then NoofRSVP should show 0 and AttendeeType will be empty (means it should ignore 'Speaker/Faculty').

Thanks for all your help...Your question is unclear, and there are some odd things in your code.

For instance, why are you left-joining the Regions table and then not referencing it anywhere?

And this clause in your WHERE statement: AND CASE @.AttendeeTypeOptionOP
WHEN '' THEN 1
WHEN 'EQ' THEN CASE
WHEN MA.AttendeeType = 1 THEN 1
ELSE 0
END
END = 1...could be written more simply like this: AND (@.AttendeeTypeOptionOP = ''
OR (@.AttendeeTypeOptionOP = 'EQ' AND MA.AttendeeType = 1))

Now, we don't know what you mean by "this query returns all 4 rows", because we don't know what you data looks like or how many records there are in each table. But since you are using an outer join on the MeetingAttendees table you can expect the query not to consider missing records in that table.