LeetCode 603. Consecutive Available Seats SQL Solution

Problem

LeetCode SQL Problem

603. Consecutive Available Seats

cinema table

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

Solution - Two Self Joins

MySQL

Find Consecutive Available Seats

SELECT C.seat_id
FROM cinema AS C
LEFT JOIN cinema AS P ON P.seat_id + 1 = C.seat_id
	AND P.free = 1
	AND C.free = 1
LEFT JOIN cinema AS N ON C.seat_id + 1 = N.seat_id
	AND C.free = 1
	AND N.free = 1
WHERE NOT (
		P.seat_id IS NULL
		AND N.seat_id IS NULL
		)

TSQL

Find Consecutive Available Seats

SELECT C.seat_id
FROM cinema AS C
LEFT JOIN cinema AS P ON P.seat_id + 1 = C.seat_id
	AND P.free = 1
	AND C.free = 1
LEFT JOIN cinema AS N ON C.seat_id + 1 = N.seat_id
	AND C.free = 1
	AND N.free = 1
WHERE NOT (
		P.seat_id IS NULL
		AND N.seat_id IS NULL
		)

The above SQL query retrieves the seat IDs of all available seats in a cinema hall which are consecutive available seats. Here is a breakdown of the different parts of the query:

• SELECT C.seat_id: This specifies the column to be retrieved from the table, which is the seat ID of the available seats.

• FROM cinema AS C: This specifies the table to be queried, which is the cinema table. The AS keyword creates an alias for the table, which is used later in the query.

• LEFT JOIN cinema AS P: This is a left join that joins the cinema table with itself based on a condition. It joins each row in the table with the row whose seat ID is one less than the current row's seat ID. The AS keyword creates an alias for the joined table, which is used later in the query.

• ON P.seat_id + 1 = C.seat_id AND P.free = 1 AND C.free = 1: This specifies the condition on which the join should be performed. It joins the rows where the seat ID of the joined row is one less than the current row's seat ID and both seats are free.

• LEFT JOIN cinema AS N: This is another left join that joins the cinema table with itself based on a condition. It joins each row in the table with the row whose seat ID is one more than the current row's seat ID.

• ON C.seat_id + 1 = N.seat_id AND C.free = 1 AND N.free = 1: This specifies the condition on which the join should be performed. It joins the rows where the seat ID of the joined row is one more than the current row's seat ID and both seats are free.

• WHERE NOT (P.seat_id IS NULL AND N.seat_id IS NULL): This specifies the condition on which the results should be filtered. It retrieves only those seats where either the seat before or after the current seat is not empty. This means that the current seat is not sandwiched between two occupied seats.

Overall, this query retrieves the seat IDs of all available seats in a cinema hall which are not sandwiched between two occupied seats.

Query Output

seat_id
3
4
5