How to find a number of consecutive rows in table

Question

I am trying to create a booking system that can select consecutive seats in order. I have a table containing the following:

ID	row	seat	status
1	A	01	1
2	A	02	1
3	A	03	1
3	A	04	1
3	A	05	1
3	A	06	1
3	B	01	1
3	B	02	1
3	B	03	1

the table could contain many rows and many seats. I am trying to query the table to find x number of seats where the row is the same and the status = 1. I've tried the following query where x = 2 but get no results:

select seat, status from seats
where seat >= (
       select a.seat
       from seats a
          left join seats b on 
             a.seat < b.seat and
             b.seat < a.seat + 2 and
             b.status = 1
       where a.status = 1
       group by a.seat
       having count(b.seat)+1 = 2
       )
group by `row` limit 2;

Any ideas

some input data, and desired output might make your problem clear. "I've tried the following query where x = 2" You did not explain what `x` is in this equation (or I did miss that....(or it was not clear)) — Luuk, Jun 14 '22 at 19:45
You are not getting results, because the SQL is throwing an error see: [DBFIDDLE](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9315a8813460fd7da86b0ae3a93bb8cf) When (trying) to program in PHP/MySQL you should always enable error reporting on your development system. (see: [How do I get PHP errors to display?](https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display)). Secondly, you should try to avoid using [reserved words](https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-R) in an SQL statement (LIKE `row`) — Luuk, Jun 15 '22 at 17:05

score 0 · Answer 1 · answered Jun 15 '22 at 15:33

You can use the following query (given X = 3 in this case):

select 
  a.row, 
  a.seat, 
  group_concat(b.status order by b.seat) as status_seq 
from seats a 
inner join seats b on 
  a.row = b.row 
  and a.seat <= b.seat 
where a.status = 1 
group by a.row, a.seat 
having status_seq like '1,1,1%';

The X = 3 is represented by the number of '1' in the last pattern. You can use concat(left(repeat('1,', 3), 3*2-1), '%') instead of this string pattern to automatically produce it, where the two '3' are X.

The idea is, for every seat where status is 1, to get the status of the next seats in the same row, including itself. This sequence is available through the status_seq value. For instance, if you set the status of seat A-03 to 0, the status_seq of each available seat will be the following:

+------+------+-------------+
| row  | seat | status_seq  |
+------+------+-------------+
| A    | 01   | 1,1,0,1,1,1 |
| A    | 02   | 1,0,1,1,1   |
| A    | 04   | 1,1,1       |
| A    | 05   | 1,1         |
| A    | 06   | 1           |
| B    | 01   | 1,1,1       |
| B    | 02   | 1,1         |
| B    | 03   | 1           |
+------+------+-------------+

Then to get 3 consecutive seats, you just have to filter this result to get every row which sequence starts with '1,1,1', using the having filter.

This would give you the following result (remember that in this example, seat A-03 is not available):

+------+------+------------+
| row  | seat | status_seq |
+------+------+------------+
| A    | 04   | 1,1,1      |
| B    | 01   | 1,1,1      |
+------+------+------------+

With X = 2:

+------+------+-------------+
| row  | seat | status_seq  |
+------+------+-------------+
| A    | 01   | 1,1,0,1,1,1 |
| A    | 04   | 1,1,1       |
| A    | 05   | 1,1         |
| B    | 01   | 1,1,1       |
| B    | 02   | 1,1         |
+------+------+-------------+

Each row in the query result is the first seat of a sequence of X available seats. You can add a final limit 1 if you want to get only the first one.

How to find a number of consecutive rows in table

1 Answers1