Why is a two-room session marked feasible when only one room is free?

Prepare the diagnostic input

Session S1 requires both A and B; S2 requires only A. An inner join followed by DISTINCT identifies sessions with any available room. Instead keep all requirement rows and compare required count with matched free-room count. Primary keys prevent repeated requirements or availability from distorting counts.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE session_requirements(session_id TEXT,room_id TEXT,PRIMARY KEY(session_id,room_id));
CREATE TABLE free_rooms(room_id TEXT PRIMARY KEY);
INSERT INTO session_requirements VALUES ('S1','A'),('S1','B'),('S2','A');
INSERT INTO free_rooms VALUES ('A');

Reproduce the misleading result and correct it

SELECT DISTINCT s.session_id FROM session_requirements s JOIN free_rooms f ON f.room_id=s.room_id ORDER BY s.session_id;

SELECT s.session_id FROM session_requirements s LEFT JOIN free_rooms f ON f.room_id=s.room_id
GROUP BY s.session_id HAVING COUNT(*)=COUNT(f.room_id) ORDER BY s.session_id;

Only A is free. The bad query proposes S1 and S2; the corrected query proposes only S2. This is a dependency-feasibility report, not a reservation: availability must be checked again under the real booking transaction.

Check the boundary before applying the fix

Add B to the snapshot. Both sessions now meet every requirement. The correction does not require exactly one room; it requires complete coverage of each session’s own list.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

INSERT INTO free_rooms VALUES ('B');

Availability is already computed for one agreed interval. Sessions with no listed requirement are absent and need a policy rather than an assumed automatic pass.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.