SQL select row(s) with max value calculated from different tables

I have the following two tables: 1. Allocation with two columns nurse and room 2. Room with two columns number and size (number of beds)

Allocation

+------+--------+<br/>
| nurse |  room   |<br/>
+------+---------+<br/>
|911923 |    1    |<br/>
|916923 |    3    |<br/>
|931923 |    1    |<br/>
|931926 |    3    |<br/>
|931927 |    4    |<br/>
|931928 |    4    |<br/>

Room

+-------+--------+<br/>
| number |  size   |<br/>
+-------+--------+<br/>
|    1   |    10   |<br/>
|    2   |    12   |<br/>
|    3   |    1    |<br/>
|    4   |    1    |<br/>
|    5   |    1    |<br/>

I need to identify all room(s) that have the highest number of nurses allocated per bed. This can be only one room or more than one if there is a tie.

The Result should show the Room number(s), which has the highest number of nurses allocated per bad.

I am not allowed to use JOIN operations. As I guess I need to select maximum value after dividing COUNT(nurse) by size and then select the rooms which correspond to that value. But, how can I do it when there are two tables?

Answer

You would use a correlated subquery if you cannot use JOIN. To get the rooms in order, then use:

select r.*,
        (select count(*) from allocation a where a.room = r.number) / r.size as nurses_per_bed
from room r
order by nurses_per_bed desc;

How you get only the maxima depends on the database. The most canonical method is to use rank() with a subquery, but there are other methods as well.