Basic SQL SUM and JOIN

Question

I am a little disoriented regarding SQL and its associations. I have two tables "hotel" and "room" .

In my room table I have a field called "hotel_id" , "capacity and " book > and in my hotel table I have a field "name" .

My "book" field is logical, therefore, if it is "0" it means that it is free, and if it is "1" it is reserved.

My main goal is to show the name of the hotel and free capacity based on available rooms, for example:

name

COSTA DEL SOL

capacity

35

(assumed to be a two-column table)

So I wrote this for starters:

SELECT * FROM room WHERE book = "0";

which gives me all the free rooms.

Then I wrote this:

SELECT hotel_id, SUM(capacity) FROM room WHERE book = "0" GROUP BY hotel_id;

which gives me all the power of the entire hotel with their identifiers

And then I wrote:

SELECT name, hotel_id FROM hotel INNER JOIN room ON hotel.id = room.hotel_id GROUP BY name;

which gives me the names of the hotels and their identifiers.

So, I lost a little by combining the sum and join request . I could not figure out how to do this.

I tried this but failed:

SELECT name, hotel_id, sum(capacity) AS hotelcapacity FROM hotel
INNER JOIN room ON hotel.id = room.hotel_id GROUP BY hotelcapacity;

If anyone could help me, that would be very grateful. Thank.

Answer 1

, , :

SELECT h.name, r.hotel_id, sum(r.capacity) AS hotelcapacity 
FROM hotel h INNER JOIN room r
ON h.id = r.hotel_id 
GROUP BY h.name, r.hotel_id

, , :

SELECT h.name, r.hotel_id, sum(r.capacity) AS hotelcapacity 
FROM hotel h INNER JOIN room r
ON h.id = r.hotel_id 
WHERE r.book = "0" 
GROUP BY h.name, r.hotel_id

Answer 2

, . .

- :

SELECT name, SUM(capacity) AS hotelcapacity 
FROM hotel
INNER JOIN room ON hotel.id = room.hotel_id 
GROUP BY name;

, , :

SELECT name, hotel_id, SUM(capacity) AS hotelcapacity 
FROM hotel
INNER JOIN room ON hotel.id = room.hotel_id 
GROUP BY name, hotel_id;

Answer 3

:

SELECT h.name, r.hotel_id, sum(r.capacity) AS hotelcapacity
FROM hotel h LEFT JOIN
     room r
     ON h.id = r.hotel_id
     WHERE r.book = "0"
GROUP BY h.name, r.hotel_id;

JOIN , , room.

. . . ( , ).