Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2010
    Posts
    4

    Unanswered: Nested Select Statement

    I need to select records from an Event database where registration for the Event has not gone past a set limit(maxRegistrations).

    SELECT eventInfo, maxRegistrations FROM eventTable WHERE parameter = x and maxRegistrations < (select COUNT(registrations) from eventRegTable)

    Is this possible? I guess my main question is the "Counting" of registrations in the WHERE Clause possible?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That is syntactically correct, the statement will run but I'd be really surprised if it does what you want/expect. Without knowing a good bit more about your eventTable, I can't help you fix your SQL statement.

    If you'd like help, please post the CREATE TABLE statement for your eventTable and at least three events worth of sample data.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, it sounds like you are doing this prior to letting someone register for the event? If that is the case just do the insert for the registration with the count being part of the insert statement.
    Code:
    INSERT INTO TABLE (COLS.....)
     SELECT DATA TO INSERT.... 
          FROM TABLE???
      WHERE .... AND MAXREGISTRATION> (SELECT TO GET THE COUNT OF REGISTRATIONS)
    Dave Nance

  4. #4
    Join Date
    Jul 2010
    Posts
    4
    Is it possible to reference a field from the top level SELECT and make the value Matches in the NESTED SELECT?

    Example:

    SELECT eventInfoID, eventInfo, maxRegistrations FROM eventTable WHERE parameter = x and maxRegistrations < (select COUNT(registrationID) from eventRegTable WHERE eventInfo.eventInfoID = registration.eventInfoID)

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT eventInfoID, eventInfo, maxRegistrations
       FROM eventTable AS a
       WHERE parameter = x
          AND maxRegistrations < (SELECT COUNT(registrationID)
             FROM eventRegTable AS b
             WHERE b.eventInfoID = a.eventInfoID)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jul 2010
    Posts
    4
    When running the following Query:

    SELECT EventID
    FROM dbo.Event AS a
    WHERE (MaxNumRSVP >
    (SELECT COUNT(rsvpID) AS Expr1
    FROM dbo.RSVP AS B
    GROUP BY rsvpID, EventID
    HAVING (a.EventID = EventID)))

    I am getting an error that the SubQuery is returning more than 1 value.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The GROUP BY is busting your chops. Try to run the inner SELECT statement using a constant for a.EventID and see what you get.

    I'd try:
    Code:
    SELECT EventID
       FROM dbo.Event AS a
       WHERE (MaxNumRSVP > (SELECT COUNT(*)
          FROM dbo.RSVP AS b
          WHERE  b.EventID = a.EventID)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jul 2010
    Posts
    4
    Brilliant! That worked.

    Thank you very much.



Posting Permissions

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