sql server - complex sql query many to many -

i've been created database that:

i want select person , movie names same person act "oyuncu" , "senarist"

please me. wrote query that;

select      moviename,personname,rolename       movies,     moviespersonrole,     personrole,     person,     role      movies.id = moviespersonrole.movieid      ,      personrole.id = moviespersonrole.personroleid      ,      personrole.roleid = role.id      ,      personrole.personid = person.id 

and 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 

you can try somethong this:

select x.moviename, x.personname (   select m.moviename, pe.personname, count(*) rolecount     movies m     inner join moviespersonrole mpr on (mpr.movieid=m.id)     inner join personrole pr on (pr.id = mpr.personroleid)     inner join person pe on (pe.id = pr.personid)     inner join role r on (r.id = pr.roleid)     r.rolename='oyuncu' or r.rolename='senarist'      group m.moviename, pe.personname   ) x x.rolecount>1