Query that finds the names of the directors that played in movies that they directed

All we need is an easy explanation of the problem, so here it is.

Given database of the website “eMovies” that manage data about cinema movies.

Write in SQL query that finds the names of the directors that played in movies that they directed

enter image description here

The tables:

Actors 
    ActorID      PK
    Firstname 
    LastName

Directors 
    DirectorID   PK
    Firstname 
    LastName

Genres 
    GenreID      PK
    GenreDescr 

Movies 
    MovieID      PK
    MovieName 
    Year

ActorsMovies 
    MovieID      PK
    ActorID      PK 

DirectorsMovies 
    MovieID      PK
    DirectorID   PK 

GenresMovies 
    MovieID      PK
    GenreID      PK 

My attempt:

 SELECT Directors.FirstName, Directors.LastName
 FROM Directors, DirectorsMovies,ActorsMovies
 WHERE Directors.DirectorID=DirectorsMovies.DirectorID=ActorsMovies.ActorID
 GROUP BY FirstName,LastName;

In the output I see an empty table, someone can tell me why and how can I improve my code? or any other working code please?.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

It’s just a set of direct joins and on the filter clause you can check the names of actor to fit of the director.

Note also this avoids cases where someone is an actor of another director movie but is also one director of another movie (where he/she is not an actor).

I used explicit join because they are prefearable over implicit ones.

select distinct d.Firstname, d.LastName
from dbo.Directors d
join dbo.DirectorsMovies dm on dm.DirectorID = d.DirectorID
join dbo.ActorMovies am on am.MovieID = dm.MovieID
join dbo.Actors a on a.ActorID = am.ActorID
where d.Firstname = a.Firstname
and d.LastName = a.LastName

Method 2

This answer based on @jean’s answer, I fixed some small mistaks this code is working:

SELECT DISTINCT Directors.FirstName, Directors.LastName
FROM ((Directors INNER JOIN DirectorsMovies ON DirectorsMovies.DirectorID = Directors.DirectorID) 
INNER JOIN ActorsMovies ON ActorsMovies.MovieID =  DirectorsMovies.MovieID)   
INNER JOIN Actors ON Actors.ActorID = ActorsMovies.ActorID
WHERE Directors.FirstName =Actors.FirstName
AND Directors.LastName = Actors.LastName;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply