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

    Question Unanswered: simple question regarding outer join (was "SQL Help")

    Hi Everyone,

    I have a simple question regarding outer join.

    Please see the attached word file. It has screen shots of the query I am running. My first query shows the result where i have M.ReservationID = MA.MeetingID and it counts NoofRSVP (# of times the query runs). I have to modify first query in such a way that it returns records from eCDReservations table even if there is no matching MeetingID in MeetingAttendees table (means Null, see the result of 2nd query in attached file). So in my result for that case NoofRSVP column should show either Null or 0.
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you want people to help you, you need to do two things:

    1. paste your query and data here, don't link to a word doc

    2. ask a question

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2006
    Posts
    13

    sql help

    my query is:
    SELECT
    M.MeetingCode,
    M.City AS MeetingCity,
    M.State AS MeetingState,
    Count(*) as NoofRSVP
    FROM
    Programs P
    INNER JOIN eCDReservations M ON (P.SubCompanyCode = M.SubCompanyCode AND P.ProgramCode = M.ProgramCode)
    LEFT JOIN MeetingAttendees MA ON (M.ReservationID = MA.MeetingID)
    INNER JOIN Attendees A ON (MA.AttendeeID = A.AttendeeID)

    WHERE
    P.SubCompanyCode = 'A011' AND
    P.ProgramCode = 'ACL52B'

    GROUP BY
    M.MeetingCode,
    M.City,
    M.State

    if I run it i get 2 rows.

    But if run following query:

    SELECT
    M.MeetingCode,
    M.City AS MeetingCity

    from
    Programs P, eCDReservations M
    WHERE
    P.SubCompanyCode = M.SubCompanyCode AND
    P.ProgramCode = M.ProgramCode AND
    P.SubCompanyCode = 'A011' AND
    P.ProgramCode = 'ACL52B'
    order by
    M.City,
    M.State

    I get 4 rows. I want all these 4 rows appear in first query result with NoofRSVP = 0 or null if there is no matching MeetingID in MeetingAttendees table.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT M.MeetingCode
         , M.City AS MeetingCity
         , M.State AS MeetingState
         , 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
     WHERE P.SubCompanyCode = 'A011' 
       AND P.ProgramCode = 'ACL52B'
    GROUP 
        BY M.MeetingCode
         , M.City
         , M.State
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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