Problem Description –
Several friends at a cinema ticket office would like to reserve consecutive available seats.
Can you help to query all the consecutive available seat orders by the seat_id using the following
Your query should return the following result for the sample case above.
Difficulty Level – Easy
SELECT DISTINCT t1.seat_id FROM cinema AS t1 JOIN cinema AS t2 ON abs(t1.seat_id - t2.seat_id) = 1 AND t1.free = 1 AND t2.free = 1 ORDER BY 1
To solve this problem, first, we need to do join the cinema table to itself using a self-join. And to find the consecutive seats we have to use
abs(t1.seat_id - t2.seat_id) = 1 because the value of the t1.seat_id will be one more or less than the value of the t2.seat_id and we also have to make sure that the seat is available by using another condition for joining the table as
t1.free = 1 AND t2.free = 1. This will give us the following output.
SELECT t1.*, t2.* FROM cinema AS t1 JOIN cinema AS t2 ON abs(t1.seat_id - t2.seat_id) = 1 AND t1.free = 1 AND t2.free = 1 ORDER BY 1
One thing to notice is that we have two values for 4 in the seat_id column because seat number 4 is next to 3 as well as next seat number 5. So, we need to use the DISTINCT keyword in the select statement in the final query.