How do I show the amount of seats left in SQL?

Admin 1
Viewed 109

I want a query that shows all screenings that:

  • Not yet has started
  • Has fewer sold tickets than the amount of seats available
  • Lastly, a own column that shows amount of tickets left

I need help with the last part.

These are the tables I've created:

CREATE TABLE Cinema (
  CityName VARCHAR(50) NOT NULL , 
  CinemaName VARCHAR(50) NOT NULL,
  AmountOfSeats INT(3) NOT NULL,
  CONSTRAINT UniqueCinema UNIQUE(CinemaName, CityName),
  PRIMARY KEY (CinemaName, CityName)
);
CREATE TABLE Screening(
    ScreeningID INT(3) NOT NULL,
    CinemaName VARCHAR(50) NOT NULL,
    CityName VARCHAR(50) NOT NULL,
    FilmName VARCHAR(50) NOT NULL,
    StartTime DATETIME NOT NULL,
    SoldSeats INT(3) NOT NULL,
    PRIMARY KEY(ScreeningID),
    CONSTRAINT CHECK(SoldSeats >= 0),
    FOREIGN KEY(CinemaName, Cityname) REFERENCES Cinema(CinemaName, CityName),
    FOREIGN KEY(FilmName) REFERENCES Film(FilmName)
);

And this is the test data:

INSERT INTO Cinema VALUES 
('Stockholm', 'Bio Rio', 150), 
('Stockholm', 'Cinemateket', 200),
('Stockholm', 'Capitol', 50),
('Uppsala', 'Fyrisbiografen', 50),
('Göteborg', 'Biopalatset', 150),
('Göteborg', 'Capitol', 50), 
('Göteborg', 'Bergakungen', 100), 
('Linköping', 'Cométen', 50),
('Jönköping', 'Fokus', 80),
('Angered', 'Biopalatset', 150);
INSERT INTO Screening VALUES 
(1, 'Capitol', 'Göteborg', 'Gudfadern', '2022-01-26 19:00:00', 30),
(2, 'Bio Rio', 'Stockholm', 'Gudfadern', '2022-01-27 19:00:00', 20),
(3, 'Bio Rio', 'Stockholm', 'Ondskan', '2022-01-29 17:00:00', 120), 
(4, 'Bio Rio', 'Stockholm', 'In the Mood for Love', '2022-01-29 13:00:00', 150), 
(5, 'Fyrisbiografen', 'Uppsala', 'Memories of Murder', '2022-01-30 19:00:00', 29), 
(6, 'Fyrisbiografen', 'Uppsala', 'Den vilda flykten', '2022-01-30 14:00:00', 19), 
(7, 'Bergakungen', 'Göteborg', 'The Death of Stalin', '2022-02-01 20:30:00', 0), 
(8, 'Fokus', 'Jönköping', 'A Bronx Tale', '2022-02-01 21:00:00', 43), 
(9, 'Biopalatset', 'Göteborg', 'Willy Wonka & the Chocolate Factory', '2022-02-13 14:00:00', 45), 
(10, 'Cinemateket', 'Stockholm', 'Monty Python and the Holy Grail', '2022-02-01 21:00:00', 50), 
(11, 'Cinemateket', 'Stockholm', 'Life of Brian', '2022-02-02 21:00:00', 50),
(12, 'Capitol', 'Stockholm', 'The Death of Stalin', '2022-02-02 19:00:00', 22),
(13, 'Cométen', 'Linköping', 'Her', '2022-02-02 14:00:00', 50),
(14, 'Cométen', 'Linköping', 'Her', '2022-02-02 18:00:00', 23),
(15, 'Biopalatset', 'Angered', 'The Rocky Horror Picture Show', '2022-02-22 19:00:00', 149),
(16, 'Capitol', 'Göteborg', 'Flight of the Navigator', '2022-02-13 13:00:00', 33),
(17, 'Capitol', 'Göteborg', 'The Great Gatsby', '2022-02-13 21:00:00', 37),
(18, 'Capitol', 'Stockholm', 'The Great Gatsby', '2022-02-13 21:00:00', 19);

This is the query I have so far, but I still want to add a new column that shows amount of available seats left:

SELECT 
    Screening.FilmName, 
    Screening.CinemaName, 
    Screening.CityName, 
    Screening.StartTime 
FROM 
    Screening 
NATURAL JOIN 
    Cinema
WHERE 
    Screening.SoldSeats < Cinema.AmountOfSeats
AND 
    Screening.StartTime >= CURDATE()
ORDER BY 
    StartTime;
mysql-workbench mysql sql
2 Answers

You can do:

select s.*, c.AmountOfSeats - s.SoldSeats as tickets_left
from Screening s
join Cinema c on c.CityName = s. CityName and c.CinemaName = s.CinemaName
where StartTime > current_timestamp()
  and s.SoldSeats < c.AmountOfSeats

Result:

 ScreeningID  CinemaName      CityName   FilmName                             StartTime            SoldSeats  tickets_left 
 ------------ --------------- ---------- ------------------------------------ -------------------- ---------- ------------ 
 1            Capitol         Göteborg   Gudfadern                            2022-01-26 19:00:00  30         20           
 2            Bio Rio         Stockholm  Gudfadern                            2022-01-27 19:00:00  20         130          
 3            Bio Rio         Stockholm  Ondskan                              2022-01-29 17:00:00  120        30           
 5            Fyrisbiografen  Uppsala    Memories of Murder                   2022-01-30 19:00:00  29         21           
 6            Fyrisbiografen  Uppsala    Den vilda flykten                    2022-01-30 14:00:00  19         31           
 7            Bergakungen     Göteborg   The Death of Stalin                  2022-02-01 20:30:00  0          100          
 8            Fokus           Jönköping  A Bronx Tale                         2022-02-01 21:00:00  43         37           
 9            Biopalatset     Göteborg   Willy Wonka & the Chocolate Factory  2022-02-13 14:00:00  45         105          
 10           Cinemateket     Stockholm  Monty Python and the Holy Grail      2022-02-01 21:00:00  50         150          
 11           Cinemateket     Stockholm  Life of Brian                        2022-02-02 21:00:00  50         150          
 12           Capitol         Stockholm  The Death of Stalin                  2022-02-02 19:00:00  22         28           
 14           Cométen         Linköping  Her                                  2022-02-02 18:00:00  23         27           
 15           Biopalatset     Angered    The Rocky Horror Picture Show        2022-02-22 19:00:00  149        1            
 16           Capitol         Göteborg   Flight of the Navigator              2022-02-13 13:00:00  33         17           
 17           Capitol         Göteborg   The Great Gatsby                     2022-02-13 21:00:00  37         13           
 18           Capitol         Stockholm  The Great Gatsby                     2022-02-13 21:00:00  19         31

See running example at DB Fiddle.

user_64361910 
Add another column in your SELECT statement (AvailableSeats).
You can add for example time left too:

SELECT 
    Screening.FilmName, 
    Screening.CinemaName, 
    Screening.CityName, 
    Screening.StartTime,
    (Cinema.AmountOfSeats - Screening.SoldSeats) as AvailableSeats,
    TIMEDIFF(Screening.StartTime, CURRENT_TIMESTAMP()) as TimeLeft
FROM 
    Screening 
JOIN 
    Cinema
WHERE 
    Screening.SoldSeats < Cinema.AmountOfSeats
AND 
    Screening.StartTime >= CURDATE()
ORDER BY 
    StartTime;
user_167445370
Related