Hey all, need some Help.

coccoster · #1 (**permalink**) 04-27-11, 06:10

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?

dav1mo · #2 (**permalink**) 04-27-11, 06:42

what are you joining the 2 tables on?
Dave

coccoster · #3 (**permalink**) 04-27-11, 21:46

not to sure, im guessing id have to use room_no?

healdem · #4 (**permalink**) 04-28-11, 02:21

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

Internet Services

Web Development

Digital Marketing

Consumer Tech