SQL Database: Display available seats for each screening

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.

Solution

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.