Select all rows that have all of the following column values

oracle

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.

Best Answer

This is one way to accomplish that, though there might be a simpler way:

WITH CTE_DirectorFilms_Distinct AS
(
    SELECT DISTINCT D.DID, F.GID
    FROM Directors D
    INNER JOIN Films F
        ON D.DID = F.DID
),
CTE_DirectorFilm_Matches AS
(
    SELECT DF2.DID, COUNT(DF1.GID) OVER (PARTITION BY DF1.DID) AS DF1_GID_COUNT, COUNT(DF2.GID) OVER (PARTITION BY DF2.DID) AS DF2_GID_COUNT
    FROM CTE_DirectorFilms_Distinct DF1
    LEFT JOIN CTE_DirectorFilms_Distinct DF2
        ON DF1.DID <> DF2.DID
        AND DF1.GID = DF2.GID
    WHERE DF1.DID = IdOfFirstDirectorGoesHere -- Or can filter on First Name and Last Name if you prefer
),
CTE_Director_Matches AS 
(
    SELECT DISTINCT DID
    FROM CTE_DirectorFilm_Matches
    WHERE DF1_GID_COUNT = DF2_GID_COUNT
)
    
SELECT D.DID, D.First_Name, D.Last_Name
FROM Directors D
INNER JOIN CTE_Director_Matches DM
    ON D.DID = DM.DID

This first gets a distinct list of all Directors and the Genres they've created a film for. Then using a left outer self join it gets all Directors with their unique Genre counts and the Genre count for the Director you're interested in (this is based on that Director's DID but you can do First_Name and Last_Name instead if you prefer). Then it filters out all Directors who don't have a matching count of unique Genres to Director of the previous CTE. Finally it gets all the fields from the Directors table for those matching Directors.

Related Question