Let's build the query step by step.
First, let's assemble the list of hotels and their free room count.
SELECT hotels.id, COUNT(*)
FROM hotels
INNER JOIN free_rooms ON(hotels.id = free_rooms.belongsToHotel)
GROUP BY hotels.id
INNER JOIN
s force rows from the table on the "left" side of the join (hotels
) only to be included in the result set when there is a corresponding table on the "right" (free_rooms
). I'm assuming here that there will only be a row in free_rooms
when the room is free.
Having this, we can now join against the list-o-nations.
SELECT hotels.id, COUNT(*), states.state
FROM hotels
INNER JOIN free_rooms ON(hotels.id = free_rooms.belongsToHotel)
INNER JOIN states ON(hotels.belongsToCountry = states.id)
GROUP BY hotels.id
It should be noted, by the way, that you've made poor choices in naming these columns. states
should be composed of id
and state_name
, hotels
should be id
, hotel_name
, state_id
, and free_rooms
should be id
, room_name
and hotel_id
. (I could also argue that states.id
should be states.state_id
, hotels.id
should be hotels.hotel_id
and free_rooms.id
should be free_rooms.room_id
because that makes the joins much easier...)
If you need to represent a "belongs to" relationship, you're actually looking for foreign key restraints. You should use those instead of special naming.
*ahem* Where was I? Oh yes. The second query will result in a result set with three columns - the hotel id, the number of rooms in it, and the country it's in. But, you just need the number of rooms per country, so let's do one last change.
SELECT COUNT(*), states.state
FROM hotels
INNER JOIN free_rooms ON(hotels.id = free_rooms.belongsToHotel)
INNER JOIN states ON(hotels.belongsToCountry = states.id)
GROUP BY states.state
Only two changes. First, we're now grouping together by state. Second, we're no longer including the hotel id in the result set. This should get you the data you need, again assuming that there will never be a row in free_rooms
when the room is not free.