Consecutive Available Seats
Problem
Cinema(seat_id, free). Return seat_ids that are free AND have a consecutive free neighbor.
c=[(1,1),(2,0),(3,1),(4,1),(5,1)][3,4,5]SELECT DISTINCT c1.seat_id
FROM Cinema c1, Cinema c2
WHERE ABS(c1.seat_id - c2.seat_id) = 1 AND c1.free = 1 AND c2.free = 1
ORDER BY c1.seat_id;Explanation
We want free seats that have a free neighbor right next to them. The trick is a self-join: we compare the Cinema table with a second copy of itself so each seat can look at the seats beside it.
FROM Cinema c1, Cinema c2 pairs every seat with every other seat. The condition ABS(c1.seat_id - c2.seat_id) = 1 keeps only pairs whose seat numbers differ by one — that is, genuine neighbors. Adding c1.free = 1 AND c2.free = 1 requires both the seat and its neighbor to be empty.
A seat can have two free neighbors (one on each side), which would list it twice, so SELECT DISTINCT c1.seat_id removes duplicates, and ORDER BY c1.seat_id returns them in order.
Example: seats 3, 4, 5 are all free. Seat 3 pairs with free seat 4, seat 4 pairs with 3 and 5, and seat 5 pairs with 4 — so all three are kept. Seat 1 is free but its only neighbor (seat 2) is taken, so it is excluded. Result: [3, 4, 5].