I have the following tables Genres, Films, Directors. They have the following schema:
CREATE TABLE GENRES( GID INTEGER PRIMARY KEY, GENRE VARCHAR(20) UNIQUE NOT NULL ); CREATE TABLE Films( FID INTEGER PRIMARY KEY, Title VARCHAR(45) UNIQUE NOT NULL, DID INTEGER NOT NULL, GID INTEGER NOT NULL, FOREIGN KEY (DID) REFERENCES Directors(DID), FOREIGN KEY (GID) REFERENCES Genres(DID) ); CREATE TABLE Directors( DID INTEGER PRIMARY KEY, First_Name VARCHAR(20) NOT NULL, Last_Name VARCHAR(20) NOT NULL );
I want to write a query that will allow me to select all of Director information for every director that has made atleast one movie in the same genre(s) as another director. For example if Stanley Kubrick has made films in genres ‘Sci-Fi’, ‘Thriller’, and ‘Crime’, I want to select all the directors who have made at least 1 sci-fi AND 1 thriller AND 1 crime film.
I’ve tried the query seen below but this will give me directors who have made atleast 1 sci-fi OR 1 thriller OR 1 crime film.
SELECT DISTINCT D.DID, D.First_Name, D.Last_Name FROM Directors D LEFT JOIN Films F ON F.DID = D.DID LEFT JOIN Genres G ON G.GID = B.GID WHERE G.Genre IN ( SELECT DISTINCT G1.Genre FROM Generes G1 LEFT JOIN Films F1 ON F1.GID = G1.GID LEFT JOIN Directors D1 ON D1.DID = D1.DID WHERE D1.First_Name = 'Stanley' AND D1.Last_Name = 'Kubrick' ); Additionally, I am not able to check before hand which Genres the director in question has been involved with. The query should work with the only given information being the Directors First and Last name.