Context: This is an assignment where I have to recreate a basic version of Netflix's movie database using SQL developer. As part of that assignment, I have to create a view that lets me see the title, year of publication, duration, description, list of directors and list of actors of a movie.
SQL developer version: 20.4.0.379.2205-x64
PL/SQL version: 11.2.0.2.0 - Production
Tables:
NETFLIX_FILMS(film_id, titre, annee_sortie, duration, description)
NETFLIX_ACTEURS(acteur_id, surnom_acteur, prenom_acteur)
NETFLIX_ACTEURS_REF(acteur_id, film_id)
NETFLIX_DIRECTEURS(directeur_id, surnom_directeur, prenom_directeur)
NETFLIX_DIRECTEURS_REF(directeur_id, film_id)
To help explain the issue I'm having, I'll use the following movie example:
Titre: The book of Eli
Directeur(s): Albert Hughes, Allen Hughes
Acteur(s): Denzel Washington, Gary Oldman, Mila Kunis
Année sortie: 2010
Duration: 118
Description: Determined to protect a sacred text that promises to save humanity, Eli goes on a quest westward across the barren, post-apocalyptic county.
I have been trying to use listagg to get the list of directors as a single string and the list of actors as a single string. However, when I try to use both listaggs in the same select statement, it creates duplicates and I can't seem to figure out how to remove them.
Here is the code I've been using:
SELECT
NETFLIX_FILMS.TITRE,
NETFLIX_FILMS.ANNEE_SORTIE,
NETFLIX_FILMS.DURATION,
NETFLIX_FILMS.DESCRIPTION,
LISTAGG(NETFLIX_DIRECTEURS.PRENOM_DIRECTEUR
|| ' ' || NETFLIX_DIRECTEURS.SURNOM_DIRECTEUR || ', ')
WITHIN GROUP (ORDER BY PRENOM_DIRECTEUR, SURNOM_DIRECTEUR)
AS NOM_DIRECTEUR,
LISTAGG(NETFLIX_ACTEURS.PRENOM_ACTEUR
|| ' ' || NETFLIX_ACTEURS.SURNOM_ACTEUR || ', ')
WITHIN GROUP (ORDER BY PRENOM_ACTEUR, SURNOM_ACTEUR)
AS NOM_ACTEUR
FROM NETFLIX_FILMS
INNER JOIN NETFLIX_ACTEURS_REF
ON NETFLIX_FILMS.FILM_ID = NETFLIX_ACTEURS_REF.FILM_ID
LEFT OUTER JOIN NETFLIX_ACTEURS
ON NETFLIX_ACTEURS_REF.ACTEUR_ID = NETFLIX_ACTEURS.ACTEUR_ID
INNER JOIN NETFLIX_DIRECTEURS_REF
ON NETFLIX_FILMS.FILM_ID = NETFLIX_DIRECTEURS_REF.FILM_ID
LEFT OUTER JOIN NETFLIX_DIRECTEURS
ON NETFLIX_DIRECTEURS_REF.DIRECTEUR_ID = NETFLIX_DIRECTEURS.DIRECTEUR_ID
GROUP BY NETFLIX_FILMS.TITRE, NETFLIX_FILMS.ANNEE_SORTIE, NETFLIX_FILMS.DURATION, NETFLIX_FILMS.DESCRIPTION;
This gives me the following:
Titre: The book of Eli
Directeur(s): Albert Hughes, Albert Hughes, Albert Hughes, Allen Hughes, Allen Hughes, Allen Hughes
Acteur(s): Denzel Washington, Denzel Washington, Gary Oldman, Gary Oldman, Mila Kunis, Mila Kunis
Année sortie: 2010
Duration: 118 Description: Determined to protect a sacred text that promises to save humanity, Eli goes on a quest westward across the barren, post-apocalyptic county.
The expected outcome is:
Titre: The book of Eli
Directeur(s): Albert Hughes, Allen Hughes
Acteur(s): Denzel Washington, Gary Oldman, Mila Kunis
Année sortie: 2010
Duration: 118
Description: Determined to protect a sacred text that promises to save humanity, Eli goes on a quest westward across the barren, post-apocalyptic county.
The directors are duplicated for each actor and the actors are duplicated for each director. This only happens when I use both listaggs in the same select statement. Listagg(distinct ...) isn't supported in this version of SQL and I can't seem to find another way to fix this.
The problem is that you have 3 actors and 2 directors and when you join all the tables together then you get 3x2 = 6 rows (as each actor is joined with each director) and then when you aggregate you get the names for all 6 rows which gives you duplicates. What you want to do is to aggregate the actors without joining the directors and aggregate the directors without joining the actors so that in both aggregations you do not generate duplicates and do not need to filter them out. You can do that in a couple of ways:
SELECT f.TITRE,
f.ANNEE_SORTIE,
f.DURATION,
f.DESCRIPTION,
( SELECT LISTAGG(
PRENOM_ACTEUR || ' ' || SURNOM_ACTEUR,
', '
) WITHIN GROUP (ORDER BY PRENOM_ACTEUR, SURNOM_ACTEUR)
FROM NETFLIX_ACTEURS_REF r
INNER JOIN NETFLIX_ACTEURS a
ON r.ACTEUR_ID = a.ACTEUR_ID
WHERE f.FILM_ID = r.FILM_ID
) AS NOM_ACTEUR,
( SELECT LISTAGG(
d.PRENOM_DIRECTEUR || ' ' || d.SURNOM_DIRECTEUR,
', '
) WITHIN GROUP (ORDER BY PRENOM_DIRECTEUR, SURNOM_DIRECTEUR)
FROM NETFLIX_DIRECTEURS_REF r
INNER JOIN NETFLIX_DIRECTEURS d
ON e.DIRECTEUR_ID = d.DIRECTEUR_ID
WHERE f.FILM_ID = r.FILM_ID
) AS NOM_DIRECTEUR
FROM NETFLIX_FILMS f;
SELECT f.TITRE,
f.ANNEE_SORTIE,
f.DURATION,
f.DESCRIPTION,
a.NOM_ACTEUR,
d.NOM_DIRECTEUR
FROM NETFLIX_FILMS f
LEFT OUTER JOIN (
SELECT r.film_id,
LISTAGG(
PRENOM_ACTEUR || ' ' || SURNOM_ACTEUR,
', '
) WITHIN GROUP (ORDER BY PRENOM_ACTEUR, SURNOM_ACTEUR)
AS NOM_ACTEUR
FROM NETFLIX_ACTEURS_REF r
INNER JOIN NETFLIX_ACTEURS a
ON r.ACTEUR_ID = a.ACTEUR_ID
GROUP BY r.film_id
) a
ON (f.FILM_ID = a.FILM_ID)
LEFT OUTER JOIN (
SELECT r.film_id,
LISTAGG(
d.PRENOM_DIRECTEUR || ' ' || d.SURNOM_DIRECTEUR,
', '
) WITHIN GROUP (ORDER BY PRENOM_DIRECTEUR, SURNOM_DIRECTEUR)
AS NOM_DIRECTEUR
FROM NETFLIX_DIRECTEURS_REF r
INNER JOIN NETFLIX_DIRECTEURS d
ON e.DIRECTEUR_ID = d.DIRECTEUR_ID
GROUP BY r.film_id
) d
ON f.FILM_ID = d.FILM_ID;