SQL Database: Display available seats for each screening

Question

Here's my database:

CREATE TABLE customer (
    id INT AUTO_INCREMENT,
    email VARCHAR(64) NOT NULL,
    password VARCHAR(32) NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB; 

CREATE TABLE movie (
    id INT AUTO_INCREMENT,
    title VARCHAR(64) NOT NULL,
    runtime TIME NOT NULL,
    certificate ENUM('U', 'PG', '12', '15', '18') NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB; 

CREATE TABLE room (
    id INT AUTO_INCREMENT,
    roomNo ENUM ('Screen 1', 'Screen 2', 'Screen 3', 'Screen 4', 'Screen 5') NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB;

CREATE TABLE seat (
    id INT AUTO_INCREMENT,
    row ENUM('A', 'B', 'C', 'D', 'E', 'F') NOT NULL,
    num ENUM('1', '2', '3', '4', '5', '6', '7', '8', '9', '10') NOT NULL,
    roomID INT,
    PRIMARY KEY (id),
    FOREIGN KEY (roomID) 
        REFERENCES room (id)
) ENGINE = InnoDB;

CREATE TABLE screening (
    id INT AUTO_INCREMENT,
    movieID INT,
    movieDate DATE NOT NULL,
    movieTime TIME NOT NULL,
    roomID INT,
    PRIMARY KEY (id),
    FOREIGN KEY (movieID) 
        REFERENCES movie (id),
    FOREIGN KEY (roomID) 
        REFERENCES room (id)
) ENGINE = InnoDB;

CREATE TABLE booking (
    id INT AUTO_INCREMENT,
    customerID INT,
    screeningID INT,
    seatID INT,
    PRIMARY KEY (id),
    FOREIGN KEY (customerID) 
        REFERENCES customer(id),
    FOREIGN KEY (screeningID) 
        REFERENCES screening (id),
    FOREIGN KEY (seatID) 
        REFERENCES seat (id)
) ENGINE = InnoDB;

And this is the query I used:

SELECT s.id, s.row, s.num, s.roomID
FROM seat s
INNER JOIN screening scr
ON scr.roomID = s.roomID
LEFT JOIN booking b
ON s.id = b.seatID
WHERE b.seatID IS NULL AND scr.id = 6

So my problem is that for screening id = 2, the roomID was 1 (meaning Screen 1). Say a customer booked seatID '1' that would mean row A, seat number 1 for screening 1. When I run this query to show available seats for another screening id where its 6 for example and the room was the same (screen 1), it would display all seats apart from seatID 1 as that was booked by another customer.

What I wanted was for each screening to have its own seat, the problem is that its for each room. So if another screening (movie) happened to use the same room, it would collide with another screening thats using the same room also.

Answer 1

The following should give you a list of all Seats available for a Screening with an id of sentinelValue ...

SELECT id AS seatID
FROM seat
WHERE roomID = ( SELECT roomID
                 FROM screening
                 WHERE screeningID = sentinelValue )
  AND id NOT IN ( SELECT seatID
                  FROM booking
                  WHERE screeningID = sentinelValue );

My logic was as follows...

To determine what Seats are still available for a certain Screening we must first know what Seats are initially available to that Screening, namely what Seats are allocated to the Room that is allocated to the Screening.

To do this we start with a known value of screening.id / screeningID, which I refer to as sentinelValue.

With this known value we can determine the room.id / roomID associated with the screening using the following SQL...

SELECT roomID
FROM screening
WHERE screeningID = sentinelValue

The database only explicitly records which seats are booked for a screening, but does not explicitly record which seats are not booked. Fortunately we can deduce which seats are not booked (i.e. are available) by comparing the list of booked seats to the complete list of seats allocated to that screening, which is the list of seats allocated to that room.

To get the list of booked seats I used the following SQL...

SELECT seatID
FROM booking
WHERE screeningID = sentinelValue

Then all that needs to be done is to get the list of seats allocated to that screening's room that are NOT yet booked. To do this I used the SQL featured at the beginning of my answer.

If you have any questions or comments, then please feel free to post a Comment accordingly.

Answer 2

I'm not sure I understood your question but you said:

What I wanted was for each screening to have its own seat

So I would just do a query with a join between screening and seats based on the roomID:

select B.id as SeatID
,B.row as SeatRow
,B.num as SeatNum
,A.roomID as RoomID
, A.id as ScreeningID
from screening A
inner join seat B on A.roomID=B.roomID

This would return you a data set with one record per seat and per screening