Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    9

    Unanswered: Hey all, need some Help.

    Hey guys.

    Doing some SQL coding and have come up with some trouble as to how i solve this query.

    Show how many bookings have been made for each room that has a spa bath

    I have two tables:

    Booking
    -Booking_no
    -Guest_no
    -Room_no
    -Date_In
    -Date_Out

    Room
    -Room_no
    -Floor_no
    -Spa_Bath
    -Num_Beds
    -Price
    -Linked_Room_No

    So far i have:

    SELECT COUNT(booking_no) , room_no
    FROM booking, room
    WHERE spa_bath = 'Y'
    group by booking.room_no

    but doesnt seem right to me LOL.

    Anyone able to help me solve this for me?

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    what are you joining the 2 tables on?
    Dave

  3. #3
    Join Date
    Apr 2009
    Posts
    9
    not to sure, im guessing id have to use room_no?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As Dav1mo suggests what you need is a mechanism to join, or realte the two tables. that means a single piece of information that is common to both tables...

    you could use an additional element in the where clause
    Code:
    ....
    WHERE spa_bath = 'Y'
    AND Booking.RoomNo = Room.RoomNo
    however that style of declaration is depracated and more modern usage would be to define a JOIN
    Code:
    SELECT COUNT(booking_no) , booking.room_no
    FROM booking
    JOIN Room on Room.RoomNo = Booking.RoomNo
    WHERE spa_bath = 'Y'
    group by booking.room_no
    MySQL :: MySQL 5.5 Reference Manual :: 12.2.9.1 JOIN Syntax

    however select count (booking_no) doesn't make sense as Im guessign you can only ever have one booking
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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