Accessing another table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Accessing another table

Author

Topic

danboy4
Starting Member

2 Posts

Posted - 2012-10-16 : 06:59:08

Hey guys, I have no idea if i'm in the right section or even the right website but I just need a little help with some basic SQL i'm doing.

I'm trying to count how many rooms in each hotel and list hotel names and count how many room numbers there are for each hotel. I have a couple tables (HOTEL, ROOM) and currently i have this:

SELECT HOTEL_NO, COUNT(ROOM_NO) "NUMBER OF ROOMS"
FROM ROOM
GROUP BY HOTEL_NO
ORDER BY HOTEL_NO;

I need to be able to also select H_NAME (which lives in the table HOTEL) so it displays the Hotel Name and the number of available bookings, not the hotel number and the number of available bookings.

I don't know if this is enough information or pointless but hopefully someone can help.
Thanks

EDIT: Here is what is produced by my statement:

H100 3
H200 5
H300 2

Here is some sample code from ROOM:

ROOM_NO, HOTEL_NO ,R_TYPE,R_PRICE
------- -------- ------ ---------
1 H100 S 220
2 H100 D 230
3 H100 F 310
1 H200 S 260
2 H200 D 170
3 H200 S 250
4 H200 F 180
5 H200 F 295
1 H300 D 200
2 H300 S 420

AND HOTEL:
HOTEL_NO H_NAME H_ADDRESS
------------------------------
H100 Sheraton Melbourne
H200 Shangra_La Sydney
H300 Hilton Perth

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-16 : 07:02:09

quote:
I need to be able to also select H_NAME (which lives in the table HOTEL) so it displays the Hotel Name and the number of available bookings, not the hotel number and the number of available bookings.

Can you explain this in a bit more detail with example data? The statement seems to be self-contradictory.

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-10-16 : 08:01:31

Hi This may help you

;WITH NOOFROOMS AS
(
SELECT HOTEL_NO, COUNT(ROOM_NO) [NUMBER OF ROOMS]
FROM ROOM
GROUP BY HOTEL_NO
)
SELECT H_NAME, [NUMBER OF ROOMS] FROM NOOFROOMS
INNER JOIN HOTEL ON HOTEL.HOTEL_NO = NOOFROOMS.HOTEL_NO
ORDER BY H_NAME

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

danboy4
Starting Member

2 Posts

Posted - 2012-10-16 : 09:27:35

Thanks! This has helped me understand the concept.

quote:
Originally posted by sql-programmers

Hi This may help you

;WITH NOOFROOMS AS
(
SELECT HOTEL_NO, COUNT(ROOM_NO) [NUMBER OF ROOMS]
FROM ROOM
GROUP BY HOTEL_NO
)
SELECT H_NAME, [NUMBER OF ROOMS] FROM NOOFROOMS
INNER JOIN HOTEL ON HOTEL.HOTEL_NO = NOOFROOMS.HOTEL_NO
ORDER BY H_NAME

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-16 : 09:32:48

simply this would do


SELECT h.H_NAME, r.NOOFROOMS 
FROM 
(SELECT HOTEL_NO,COUNT(ROOM_NO) AS NOOFROOMS 
FROM ROOM 
GROUP BY HOTEL_NO)r
INNER JOIN HOTEL h
ON h.HOTEL_NO = r.HOTEL_NO
ORDER BY h.H_NAME


or this if you're on SQL 2005 or above with compatibility level at least 90


SELECT DISTINCT h.H_NAME, COUNT(r.ROOM_NO) OVER (PARTITION BY h.HOTEL_NO) AS NOOFROOMS 
FROM ROOM r
INNER JOIN HOTEL h
ON h.HOTEL_NO = r.HOTEL_NO
ORDER BY h.H_NAME

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources