Spencer Lim Spencer Lim - 7 months ago 13
SQL Question

Counting field total against another table column value

room | beds available | ****table room****
==================================
room1 | 4
room2 | 2
room3 | 4

room | occupant | ****table occupant****
==================================
room1 | arnold
room1 | berry
room2 | charles
room2 | daisy
room3 | eric
room3 | frank
room3 | greg


I looking to get the following output:

No. of rooms with beds available: 2 || Rooms with beds available: room 1, room 3

i reckon i need to


  1. store count* for each unique room and store the count as an array

  2. subtract beds available for each room against this array

  3. display the room name when there's a result > 0



How should the php snippet code look like?

Answer

These SQL queries will do the trick.

Remember, you can remove unwanted columns to save the amount of data you're processing...

select r.id AS 'room',
    r.beds as 'total_beds',
    count(o.occupant) as 'taken_beds',
    r.beds-count(o.occupant) as 'free_beds'

FROM room r LEFT JOIN occupant o ON r.id = o.room
GROUP BY r.id

HAVING r.beds > count(o.occupant)

This will only return the rooms that are not full.

MySQL Result

If at any other point, you wish to return full rooms too, simply remove the "HAVING" clause

select r.id AS 'room',
    r.beds as 'total_beds',
    count(o.occupant) as 'taken_beds',
    r.beds-count(o.occupant) as 'free_beds'

FROM room r LEFT JOIN occupant o ON r.id = o.room
GROUP BY r.id

MySQL Result without HAVING clause