Problem joining separate tables

PHP
41

Hi Rudy,

I have a page where I display all hotels in a certain area. Each hotel will have a number of rooms, each room having a number of beds. So the SUM() function should display the total number of beds for each hotel.

if the former, why would a room have more than one row in the rooms table?

No room has more than one row in the rooms table.

if the latter, why do you need to see any room columns at all?

I don’t understand this question.

Thanks for your help!

42 
SELECT h.hotel_id
     , h.hotel_name
     , h.address
     , h.city
     , h.state
     , h.country
     , h.zipcode
     , h.phone
     , r.total_beds
     , hf.features_id
     , hf.feature_name
     , f.feature_description 
  FROM hotels AS h  
INNER 
  JOIN ( SELECT hotel_id
              , SUM(room_beds) AS total_beds
           FROM rooms 
         GROUP
             BY hotel_id ) AS r  
    ON r.hotel_id = h.hotel_id  
INNER 
  JOIN hotels_features AS hf  
    ON hf.hotel_id = h.hotel_id  
INNER 
  JOIN features AS f  
    ON f.features_id = hf.features_id 
 WHERE h.city = 'berlin'

you want the query to give the sum of all room beds for each hotel

so which room did you want to display as well? you had r.room_id, r.room_name, r.room_floor, r.room_number, and r.room_description in your query

43

Hi Rudy,

thank you very much for the code! It is working perfectly as far as I can tell. What a relief.

so which room did you want to display as well? you had r.room_id, r.room_name, r.room_floor, r.room_number, and r.room_description in your query

To be honest those other room columns aren’t really necessary, so I’ve removed them.

Once again, thank you for your time and help :slight_smile:

Cheers