I can't combine these two selects

[kenw232]

I'm just an beginner SQL guy and can't figure out how to do this.  Of if its even possible. 

 

This select gets a list of the hotels that are assigned to a given event, in this case event number 78:

select * from EventsHotels, Hotels where EventsHotels.event_num = '78' and EventsHotels.hotel_num = Hotels.hotel_num and Hotels.active = 'on' order by Hotels.hotel_num;
Returns something like:
+-----------+---------------------------+
| hotel_num | many more columns|
+-----------+---------------------------+
|        58 | many values                +
|        59 | many values                +
|        77 | many values                +
+----------------------------------------+

This separately gets the number of rooms free for give hotel.  In this case hotel 77:

select sum(room_type_1 + room_type_2 + room_type_3 + room_type_4 + room_type_5 + room_type_6 + room_type_7 + room_type_8 + room_type_9 + room_type_10 + room_type_11 + room_type_12)
as total_rooms from HotelsInventory where inv_date >= '2015-12-01' and inv_date < '2015-12-31' and hotel_num = '77';
+-------------+
| total_rooms |
+-------------+
|           5 |
+-------------+

The objective is to simply combine the two statements into one large SQL statement and sort by total_rooms.  Anyone know how to do this?

MySQL 5.1.46

Edited December 20, 2015 by kenw232

[Barand]

Whenever you have column names like room_type_1, room_type_2, ..., room_type_N, the database design is wrong. Database tables are not spreadsheets. You should normalize the data so each of those room_type values is in a row of its own. (See attached model.).

 

Don't use SELECT *. Specify just the column you need.

 

Use explicit join syntax instead of putting the join conditions in the WHERE clause.

 

Using the attached model, the query you want would be

SELECT 
  h.hotel_num
, h.name
, SUM(hi.qty) as rooms
FROM hotel h 
INNER JOIN event_hotels eh
    ON h.hotel_num = eh.hotel_num
INNER JOIN hotels_inventory hi
    ON h.hotel_num = hi.hotel_num
WHERE eh.event_num = 78
AND hi.inv_date BETWEEN '2015-12-01' and '2015-12-31'
GROUP BY h.hotel_num
ORDER BY rooms DESC;

[kenw232]

Thank you for the help.

[Barand]

Here's the model I forgot to attach