StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Let's build the query step by step.</p> <p>First, let's assemble the list of hotels and their free room count.</p> <pre><code>SELECT hotels.id, COUNT(*) FROM hotels INNER JOIN free_rooms ON(hotels.id = free_rooms.belongsToHotel) GROUP BY hotels.id </code></pre> <p><code>INNER JOIN</code>s force rows from the table on the "left" side of the join (<code>hotels</code>) only to be included in the result set when there is a corresponding table on the "right" (<code>free_rooms</code>). I'm assuming here that there will <em>only be a row in <code>free_rooms</code> when the room is free</em>.</p> <p>Having this, we can now join against the list-o-nations.</p> <pre><code>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 </code></pre> <p>It should be noted, by the way, that you've made poor choices in naming these columns. <code>states</code> should be composed of <code>id</code> and <code>state_name</code>, <code>hotels</code> should be <code>id</code>, <code>hotel_name</code>, <code>state_id</code>, and <code>free_rooms</code> should be <code>id</code>, <code>room_name</code> and <code>hotel_id</code>. (I could also argue that <code>states.id</code> should be <code>states.state_id</code>, <code>hotels.id</code> should be <code>hotels.hotel_id</code> and <code>free_rooms.id</code> should be <code>free_rooms.room_id</code> because that makes the joins much easier...)</p> <p>If you need to represent a "belongs to" relationship, you're actually looking for <a href="http://en.wikipedia.org/wiki/Foreign_key" rel="nofollow">foreign key restraints</a>. You should use those instead of special naming.</p> <p>*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.</p> <pre><code>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 </code></pre> <p>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 <code>free_rooms</code> when the room is not free. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload