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
('i',1,0),('i',2,0),('i',3,0),('i',4,0),('i',5,0),('i',6,0),('i',7,0),('i',8,0),
('i',9,0),('i',10,0),('i',11,0),('i',12,0),('i',13,0),('i',14,0),('i',15,0),
('j',1,1),('j',2,0),('j',3,1),('j',4,0),('j',5,0),('j',6,0),('j',7,1),('j',8,0),
('j',9,0),('j',10,0),('j',11,0),('j',12,0),('j',13,1),('j',14,0),('j',15,0); 

The simplest method is a self-join:

-- two adjacent seats
select a.row,a.seat,b.seat
from seats a
join seats b on a.row=b.row and a.seat=b.seat-1 
where a.booked=0 and b.booked=0
limit 1;
+------+------+------+
| row  | seat | seat |
+------+------+------+
| i    |    1 |    2 |
+------+------+------+

-- three adjacent seats
select a.row,a.seat,b.seat,c.seat
from seats a
join seats b on a.row=b.row and a.seat=b.seat-1 
join seats c on a.row=c.row and b.seat=c.seat-1 
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
FROM (
  SELECT first.row, first.seat AS firstUnused, MIN(c.seat) AS mincseat, MAX(d.seat) AS dseat 
  FROM (
    SELECT a.row, a.seat 
    FROM seats a 
    LEFT JOIN seats b ON a.row=b.row AND a.seat=b.seat + 1
    WHERE a.booked=0 AND (b.seat IS NULL OR b.booked=1)
  ) AS first
  LEFT JOIN seats c ON first.seat < c.seat AND c.booked=1 AND c.row = first.row
  LEFT JOIN seats d ON first.seat < d.seat AND d.booked=0 AND d.row = first.row
  GROUP BY firstUnused
) AS e
HAVING lastUnused-firstUnused>=N;   -- N=no.of required adjacent seats


Return to the Artful Common Queries page