SQL insert query with some condition

I am using MySQL.

There are three tables presented: Patient, Occupies, Room and Privte_Patient. I need to identify the first available room and allocate the room to a newly admitted patient with PIN ‘314’ (the patient is already added to a database). Note that the room should be either single or multiple occupancy depending whether the patient is private or not.

• As a result, I want get the SQL query which will allocate the patient with PIN ‘314’ to the room number 1. Because this patient is not a private patient and room number 1 is the first room with empty bed (size is 2 beds).

Any idea of how to identify this room? Can I do it using Conditional INSERT?

Table Patient

+-------+---------+
| PIN   |  name   |
+-------+---------+
|314    |  Lana   |
|778899 | Michael |
|345566 |  Jone   |
+-------+---------+

Table Occupies

+--------+--------+
|patient |  room  |
+--------+--------+
|778899  |    1   |
|345566  |    4   |
+-------+---------+

Table Room

+--------+--------+
|number  |  size  |
+--------+--------+
|  1     |    2   |
|  2     |    12  |
|  3     |    1   |
|  4     |    1   |
+-------+---------+

Private_Patient

+--------+--------+
|patient |consultant|
+--------+--------+
|345566  |  345566  |

+-------+---------+

Answer

Consider:

insert into occupies(patient, room)
select 314, r.number
from room r
left join (select room, count(*) size from occupies group by room) o
    on o.room = r.number
where coalesce(o.size, 0) < r.size
order by r.number
limit 1

For each room, the query brings the number of occupants and uses that information to filter out rooms that are full already. The first room where at least in bed is available is selected.

It might be simpler to understand with a correlated subquery:

insert into occupies(patient, room)
select 314, r.number
from room r
where r.size > (
    select count(*)
    from occupies o
    where o.room = r.number
)
order by r.number
limit 1