Find adjacent unbooked theatre seats

from the Artful Common Queries page

A theatre booking service is often asked to book adjecent seats:
drop table if exists seats;
create table seats(row char(1),seat int,booked tinyint);
insert into seats values
The simplest method is a self-join:
-- two adjacent seats
select a.row,,
from seats a
join seats b on a.row=b.row and 
where a.booked=0 and b.booked=0
limit 1;
| row  | seat | seat |
| i    |    1 |    2 |

-- three adjacent seats
select a.row,,,
from seats a
join seats b on a.row=b.row and 
join seats c on a.row=c.row and 
where a.booked=0 and b.booked=0 and c.booked=0
limit 1;
| row  | seat | seat | seat |
| i    |    1 |    2 |    3 |
But that approach requires a different query for each different number of adjacent seats. The Find blocks of unused numbers query pattern can be used to find all empty seats. Optionally you can add a HAVING clause specifying the number of adjacent seats required:
SELECT e.row, firstUnused, IF(mincseat IS NULL, IFNULL(dseat,firstUnused),mincseat-1) AS lastUnused
  SELECT first.row, AS firstUnused, MIN( AS mincseat, MAX( AS dseat 
  FROM (
    SELECT a.row, 
    FROM seats a 
    LEFT JOIN seats b ON a.row=b.row AND + 1
    WHERE a.booked=0 AND ( IS NULL OR b.booked=1)
  ) AS first
  LEFT JOIN seats c ON < AND c.booked=1 AND c.row = first.row
  LEFT JOIN seats d ON < AND d.booked=0 AND d.row = first.row
  GROUP BY firstUnused
) AS e
HAVING lastUnused-firstUnused>=N;   -- N=no.of required adjacent seats

Last updated 26 Oct 2010