Query involving counting and comparing

Databases
1

Suppose I have two tables, one representing the houses on a street and the other representing the people who live on that street:

mysql> select * from Buildings
+------------+--------+
| BuildingID | maxocc |
+------------+--------+
| 1          | 3      |
| 2          | 5      |
| 3          | 2      |
+------------+--------+

# "maxocc" refers to the maximum number of people who
# can live in a particular building

mysql> select * from Residents
+----------+--------+
| PersonID | HomeID |
+----------+--------+
| 1        | 1      |
| 2        | 3      |
| 3        | 2      |
| 4        | 2      |
| 5        | 1      |
| 6        | 3      |
| 7        | 2      |
+----------+--------+

What I’m trying to design is an efficient query that will return the “spaces left” in each building. The “spaces left” would be Buildings.maxocc minus the number of rows in Residents whose HomeID=Buildings.BuildingID. For example, on these two tables, it should return something like:


+------------+------------+
| BuildingID | spacesleft |
+------------+------------+
| 1          | 1          |
| 2          | 2          |
| 3          | 0          |
+------------+------------+

Also, I would like the ability to return only those buildings whose “spaces left” is greater than 0. In the scenario above, it would only return buildings 1 and 2.

I have made a clunky query using a subquery that takes a long time to execute but does work:

select t1.BuildingID, t1.maxocc-(
    select count(*)
    from Residents a1
    where a1.HomeID=t1. BuildingID
) as spacesleft
from Buildings t1

However, I don’t think there would be a way to limit results to instances when spacesleft (which doesn’t really exist in the table) is greater than 0.

Is there a way to make a cleaner query, maybe using joins or something, that is both efficient and allows for returning rows based on the result of the “spacesleft” calculation?

2 
SELECT BuildingID
     , spacesleft
  FROM ( SELECT t1.BuildingID
              , t1.maxocc - m1.occ AS spacesleft
           FROM Buildings AS t1
         INNER
           JOIN ( SELECT HomeID
                       , COUNT(*) AS occ
                    FROM Residents
                  GROUP
                      BY HomeID ) AS m1
             ON m1.HomeID = t1. BuildingID
       ) AS d
 WHERE spacesleft > 0
3

run an EXPLAIN on the query, to see how efficient it will be

4

r937,

Thanks for your reply!

Although the query you suggested would enable results to be limited based on their “spacesleft” value, it has not only one but two subqueries. I need to plan to have this query run on tables that could potentially become several megabytes large, so efficiency is important (although the query would consider no more than a few hundred rows at a time). Would this query be too strenuous on the processor?

If there’s no cleaner alternative, I can just eliminate certain rows in the PHP script that originally runs the query.