Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: Using the Count expression

    How can I use the Count expression to achieve the following:

    availability table
    room_id property_id date_id available
    1 8 1 Yes
    1 8 2 Yes
    1 8 3 Yes
    2 8 1 Yes
    2 8 2 No
    2 8 3 Yes

    Select the room ids that are available for all three date ids without using repetitive AND statements and instead using the following SQL with a count = 3 expression.

    SELECT room_id
    FROM availability
    WHERE availability.property_id=8
    AND availability.available=Yes
    AND date_id BETWEEN 1 AND 3

    This returns:
    room_id
    1
    1
    1
    2
    2

    but I only want to return the room_ids that are available for all 3 date_ids i.e. room_id 1.

    so I could use the DISTINCT key word but this would return:
    room_id
    1
    2

    so I need to also say in an expression "where count each room_id = 3".

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Query like this one does what you wanted, but I'm sure there must be a better way to do it ...
    PHP Code:
    SELECT b.room_id
      FROM availability b
      WHERE b
    .property_id 
        
    AND b.date_id BETWEEN 1 AND 3
      GROUP BY b
    .room_id
      HAVING 3 
    = (SELECT COUNT(*) FROM availability b1
                  WHERE b1
    .available 'Yes'
                 
    AND b1.room_id b.room_id
                 
    );

       
    ROOM_ID
    ----------
             
    1
    1 row selected 

Posting Permissions

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