mysql - SQL - Select rows where one element appears twice -

mysql - SQL - Select rows where one element appears twice -

i working on assignment, , need find movies have been directed directors directed more 1 film starring angelina jolie. currently, have this:

select distinct t.title, n.name ( select distinct t.id themovies name n inner bring together cast_info c on (c.person_id = n.id) inner bring together title t on (t.id = c.movie_id) n.name = 'jolie, angelina' ) newtable inner bring together title t on (t.id = themovies) inner bring together cast_info c on (c.movie_id = t.id) inner bring together name n on (n.id = c.person_id) cross bring together role_type role = 'director';

what query find list of movies starring angelina jolie, , lists directors of movies. need maintain rows director nowadays in @ to the lowest degree 1 other row. tips? reference, here diagram of database i'm using: http://i.imgur.com/kj8qvgf.png i'm rather new sql suggestions improve query much appreciated!

i break several pieces , build final query. if new sql, it's practice break things bits , set them together. that, i'll restate goal: find movies have been directed directors have directed film angelina jolie.

i start getting movies angelina jolie:

select t.id name n bring together cast_info c on c.person_id = n.id bring together title t on t.id = c.movie_id n.name = 'jolie, angelina';

now, let's directors of movies:

select c.person_id cast_info c bring together title t on t.id = c.movie_id bring together role_type r on r.id = c.role_id r.role = 'director' , t.id in(select t.id name n bring together cast_info c on c.person_id = n.id bring together title t on t.id = c.movie_id n.name = 'jolie, angelina');

we can modify above query grouping person_id, having count(*) greater 1 (meaning more 1 movie).

select c.person_id cast_info c bring together title t on t.id = c.movie_id bring together role_type r on r.id = c.role_id r.role = 'director' , t.id in(select t.id name n bring together cast_info c on c.person_id = n.id bring together title t on t.id = c.movie_id n.name = 'jolie, angelina') grouping person_id having count(*) > 1;

now, need find movies directed directors, , filter don't include movies angelina jolie.

select t.id title t bring together cast_info c on c.movie_id = t.id bring together role_type r on r.id = c.role_id r.role = 'director' , c.person_id in (select c.person_id cast_info c bring together title t on t.id = c.movie_id bring together role_type r on r.id = c.role_id r.role = 'director' , t.id in(select t.id name n bring together cast_info c on c.person_id = n.id bring together title t on t.id = c.movie_id n.name = 'jolie, angelina') grouping person_id having count(*) > 1) , t.id not in(select t.id name n bring together cast_info c on c.person_id = n.id bring together title t on t.id = c.movie_id n.name = 'jolie, angelina');

i can't test via sql fiddle because not working @ moment can. stuff might need tweaked, allow me know if helps.

mysql sql database