Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006
    Posts
    13

    Question Unanswered: 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...

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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:
    Code:
    	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:
    Code:
    	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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •