Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006

    Question Unanswered: Simple SQL Question

    I have written following query for a stored procedure:

    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

    Programs P
    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
    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.

  2. #2
    Join Date
    Nov 2005
    San Francisco, CA
    Can you paste the resultset you are getting now and the resultset you want?
    That will make things clear, and if possible the table stucture and put it within <code> </code>.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill

  3. #3
    Join Date
    Jul 2003
    San Antonio, TX
    Since Attendees will have no corresponding rows, and consequently the value for Status will be null (due to LEFT OUTER JOIN), you will have to modify your filtering condition:

    ...WHERE (isnull(MA.Status, 2)=2 or isnull(MA.Status, 4)=4)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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