SQL-statement til gennemsyn af SQL-hajer
Hejsa,Vi har fået en opgave om at lave et bestemt udtræk. Databasen skulle gerne afspejle imdb.com's og de relevante tabeller ser ud som følger:
CREATE TABLE person (
Id int(11) auto_increment,
name varchar(255),
gender enum('Male','Female'),
dateOfBirth date,
dateOfDeath date,
PRIMARY KEY (Id)
);
CREATE TABLE movie (
Id int(11) auto_increment,
name varchar(255),
runtime int(3),
rating double(2,1),
productionYear int(4),
language varchar(255),
PRIMARY KEY (Id),
CHECK(rating<10,1)
);
CREATE TABLE acts(
actorId int(11),
movieId int(11),
role varchar(255),
PRIMARY KEY(actorId, movieId),
FOREIGN KEY (actorId) REFERENCES person(Id),
FOREIGN KEY (movieId) REFERENCES movie(Id) ON DELETE CASCADE
);
Vi skal herefter lave et udtræk, der sørger for følgende:
Which actors in Pulp Fiction have never, before or after, starred in the same movie as one of the other actors in Pulp Fiction.
Og her er vores svar. Det er en noget roddet affære, og derfor er vi lidt i tvivl om, at det nu også er det smarteste?
SELECT r.actorId FROM (
SELECT a.actorId FROM acts a WHERE a.movieId IN(
SELECT m.Id FROM movie m WHERE m.name = 'Pulp Fiction')) AS r
WHERE r.actorId NOT IN
(SELECT a.actorId FROM acts a WHERE a.movieId IN (
SELECT t.movieId FROM (
SELECT a.movieId, COUNT(a.movieId) AS movieCount FROM acts a WHERE a.actorId IN(
SELECT a.actorId FROM acts a WHERE a.movieId IN(
SELECT m.Id FROM movie m WHERE m.name = 'Pulp Fiction')) GROUP BY a.movieId) AS t
WHERE t.movieCount > '1' AND t.movieId != (
SELECT m.Id FROM movie m WHERE m.name = 'Pulp Fiction')
)
);
Jeg håber på nogle skarper hjerner sidder derude :)
Mvh Søren