Several friends at a cinema ticket office would like to reserve consecutive available seats.

Can you help to query all the consecutive available seats order by the seat_id using the following

cinema

table?

| seat_id | free |
|---------|------|
| 1       | 1    |
| 2       | 0    |
| 3       | 1    |
| 4       | 1    |
| 5       | 1    |

Your query should return the following result for the sample case above.

| seat_id |
|---------|
| 3       |
| 4       |
| 5       |

Note

:

The seat_id is an auto increment int, and free is bool ('1' means free, and '0' means occupied.).

Consecutive available seats are more than 2(inclusive) seats consecutively available.

Solution

Approach: Using selfjoinandabs()[Accepted]

Intuition

There is only one table in this problem, so we probably need to useself joinfor this relative complex problem.

Algorithm

First, let's see what we have after joining this table with itself.

Note: The result of join two tables is theCartesian productof these two tables.

select a.seat_id, a.free, b.seat_id, b.free from cinema a join cinema b;

To find the consecutive available seats, the value in the a.seat_id should be more(or less) than the value b.seat_id, and both of them should be free.

select a.seat_id, a.feee, b.seat_id, b.free 
from cinema a join cinema b on abs(a.seat_id - b.seat_id) = 1 and a.free = true and b.free = true;

At last, choose the concerned column seat_id, and display the result ordered by seat_id.

Note: You may notice that the seat withseat_id'4' appears twice in this table. This is because seat '4' next to '3' and also next to '5'. So we need to usedistinctto filter the duplicated records.

MySQL

select
distinct a.seat_id from cinema a join cinema b on abs(a.seat_id - b.seat_id) = 1 and a.free = true and b.free = true 
order by a.seat_id;

another solution:

select seat_id

from cinema

where free = true and (

seat__id + 1 in (select _seat_id from cinema where free = true) or

seat_id - 1 in (select seat_id from cinema where free = true)

)