Trouble with homework

[teddarr]

OK I'm having trouble with this problem on my homework. I got the rest OK, I don't know why this problem is stumping me.

List the number of rooms in each hotel.

Tables:
HOTEL: (hotelNo, hotelName, city)
ROOM: (roomNo, hotelNo, type, price)
BOOKING: (hotelNo, guestNo, dateFrom, DateTo, roomNo)
GUEST: (guestNo, guestName, guestAddress)

here's what I have tried, but it's not working.....

SELECT hotelNo, COUNT (roomNo) AS roomcount
FROM room
WHERE (SELECT DISTINCT hotelNo FROM hotel)
ORDER BY hotelNo

and

SELECT COUNT (roomNo)
FROM room
WHERE h.hotelNo = r.hotelNo
Order By hotelNo

Any clues or advice and any help will be appreciated.

[Purple]

Hi teddarr

what is in table room, column roomNo ? is there one record for every room in every hotel or is it one row per hotel with the number of rooms in it ?

Purple

[teddarr]

table room contains one row for every room in every hotel. The table also contains hotelNo which gives the distinct hotel.

[debasisdas]

Is there any cascade dependency with other tables.

Can you please post what exactly is the error you are getting.

[Purple]

Hi teddar,

I think this should do what you need...

SELECT    hotel.hotelNo, COUNT(room.roomNo) AS num_rooms
FROM         hotel INNER JOIN
                      room ON hotel.hotelNo = rooom.hotelNo
GROUP BY hotel.hotelNo, room.roomNo

I think you need to do some reading around joins and group by..

Regards Purple

[teddarr]

Purple,

Your recommendation on reading is right on. That is exactly where my mental hang up is.

I tried your statements and even added a line that reads WHERE hotel.hotelNo = room.hotelNo

The result I get is about 5 entries with the number 1 next to each hotelNo instead of a count.

example:
hotelNo num_rooms
1 1
1 1
1 1
1 1
1 1
1 1
1 1
2 1
2 1
2 1
2 1
3 1
3 1
3 1
3 1
3 1

This gets me closer but I still don't know what to do from here.

[Purple]

Hi,

is hotel.hotelNo unique ?

for info - WHERE hotel.hotelNo = room.hotelNo is not required - the join is doing this for you in this : hotel.hotelNo = rooom.hotelNo

do me a :

select * from hotel

and

select * from room

and post some of the output into the thread plse

Purple

[teddarr]

I got it!

Thanks Purple. With yours and the guys at work giving advice, I now have a solution:

SELECT hotel.hotelNo, COUNT(room.roomNo) AS numRooms
FROM hotel INNER JOIN room ON hotel.hotelNo = room.hotelNo
GROUP BY hotel.hotelNo;

You and the guys at work both pointed out that the WHERE clause was redundant. Thanks for the heads up.

[prasad2007]

Hi,

This is May be Help For u


SELECT H.HotelNo as HotelCode,Count(R.RoomNo),H.CITY as NoOfRooms
FROM Hotel H INNER JOIN ROOM R ON
H.HotelNo=R.HotelNo
Group By H.HotelNo,H.CITY


Bye