How to solve this specific SQL query? My solution also returns not wanted values-CodePudding

I have three tables in my DB:

"Film", which contains details regarding movies
"Sale", which contains details regarding theathers where movies are projected
"Proiezioni", which contains details regarding projections, with external references to both the previous tables

The problem involves finding the titles of all the movies that were projected in the city of "Pisa" only.

select f.Titolo
from Film f
where not exists(select *
                 from Sale s, Proiezioni p
                 where p.xCodSala=s.CodSala AND f.CodFilm=p.xCodFilm AND Citta<>'Pisa');

The problem of this solution is that it gives me the movies that were screened only in "Pisa", but also the (non-requested) movies that weren't screened at all (not present in the table "Proiezioni").

You can find the code for tables generation and population (ddl) below.

The expected output is only one row corresponding to the movie title "I Predatori" having "CodFilm = 'f03'".

create table Film (
    CodFilm char(6) not null,
    Titolo varchar(30) not null,
    AnnoProduzione int(4) null,
    Nazionalita varchar(20) null,
    Regista varchar(30) null,
    Genere varchar(15) null,
    PRIMARY KEY (CodFilm)
) engine=innodb;

create table Sale
(
    CodSala char(3) not null,
    Posti int(3) not null,
    Nome varchar(10) not null,
    Citta varchar(20) not null,
    PRIMARY KEY (CodSala)
) engine=innodb;

create table Proiezioni
(
    CodProiezione varchar(8) not null,
    xCodFilm char(6) not null,
    xCodSala char(3) not null,
    Incasso real not null,
    DataProiezione date not null,
    PRIMARY KEY (CodProiezione),
    FOREIGN KEY (xCodFilm) references Film (CodFilm) on delete cascade,
    FOREIGN KEY (xCodSala) references Sale (CodSala) on delete cascade
) engine=innodb;

insert into Film values
    ("f01", "Wall Street",1987,"Stati Uniti","Oliver Stone","Drammatico"),
    ("f02","Nothing Hill",1999,"Regno Unito","Roger Michell","Commedia"),
    ("f03","I Predatori",2020,"Italia","Pietro Castellitto","Commedia"),
    ("f04","Fight Club",1999,"Stati Uniti","David Fincher","Drammatico"),
    ("f05","Il mattino ha l'oro in bocca",2008,"Italia","Francesco Patierno","Drammatico"),
    ("f06","Basic Instinct",1992,"Stati Uniti","Paul Verhoeven","Thriller"),
    ("f07","About A Boy",2002,"Regno Unito","Paul Weitz, Chris Weitz","Commedia"),
    ("f08","Troy",2004,"Stati Uniti","Wolfgang Petersen","Azione"),
    ("f09","Platoon",1986,"Stati Uniti","Oliver Stone", "Guerra"),
    ("f10","Seven",1995,"Stati Uniti","David Fincher","Thriller");
    
insert into Sale values
    ("s01", 100, "Cineworld", "Roma"  ),
    ("s02", 125, "Madison"  , "Rieti" ),
    ("s03",  90, "UCI"      , "Milano"),
    ("s04", 110, "The Space", "Milano"),
    ("s05",  50, "Greenwich", "Pisa"  );
    
insert into Proiezioni values
    ("p01","f04","s01",250000.50,'2022-01-07'),
    ("p02","f08","s03",300000.95,'2022-01-07'),
    ("p03","f08","s05",90000.00,'2022-02-05'),
    ("p04","f02","s01",18000,'2022-03-18'),
    ("p05","f02","s02",35775.80,'2022-04-20'),
    ("p06","f01","s04",92500.40,'2022-05-10'),
    ("p07","f09","s03",430000.45,'2022-05-13'),
    ("p08","f09","s03",22000,'2022-05-14'),
    ("p09","f03","s05",66000,'2022-05-20'),
    ("p10","f01","s02",280000,'2022-05-22'),
    ("p11","f01","s02",70000,'2022-05-23'),
    ("p12","f04","s04",16500,'2022-05-25');

CodePudding user response：

SELECT Film.Titolo
FROM Film
JOIN Proiezioni ON Film.CodFilm = Proiezioni.xCodFilm
JOIN Sale ON Proiezioni.xCodSala = Sale.CodSala
GROUP BY 1
HAVING COUNT(*) = SUM(Sale.Citta = 'Pisa')

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ac20c252cd148b2c6a01ae7e76448103

CodePudding user response：

You should first inner join "film" with "proiezioni", such that you'll ensure to get projected films. Then you can exclude all theathers where the film was projected in any city other than "Pisa", which will output films that were projected in Pisa only.

SELECT DISTINCT f.Titolo
FROM       Film       f
INNER JOIN Proiezioni p
        ON f.CodFilm = p.xCodFilm 
WHERE p.xCodFilm NOT IN (SELECT p.xCodFilm 
                         FROM       Proiezioni p 
                         INNER JOIN Sale       s
                                 ON p.xCodSala = s.CodSala
                         WHERE s.Citta <> 'Pisa')

Check the demo here.