complex sql query many to many

I’ve been created my database like that:

My database diagram

I want to select person and movie names which is same person act as “Oyuncu” and “Senarist”

Please help me. I wrote a query like that;

select 
    MovieName,PersonName,RoleName 
from 
    Movies,
    MoviesPersonRole,
    PersonRole,
    Person,
    Role
where 
    Movies.Id = MoviesPersonRole.MovieId 
    and 
    PersonRole.Id = MoviesPersonRole.PersonRoleId 
    and 
    PersonRole.RoleId = Role.Id 
    and 
    PersonRole.PersonId = Person.Id

and the results are

MovieName         PersonName    RoleName
Pulp Fiction       Mehmet        Oyuncu
Pulp Fiction       Mehmet        Senarist
Matrix             Aylin         Oyuncu
LOTR               Gökberk       Oyuncu
LOTR               Gökberk       Senarist
Pulp Fiction       Aylin         Oyuncu


Source: sql

Leave a Reply