0

Please login or register to vote for this query.

(click on this box to dismiss)

Enter Query Description

Q&A for anime and manga fans

-- Enter Query Title
-- Enter Query Description

DECLARE @Film table (person_name varchar(50), 
film_title varchar(50), relationship varchar(10))
Insert into @film  values ('Mel Gibson', 'Braveheart', 'director' ),
                          ('Mel Gibson', 'Braveheart', 'cast' ),
                          ('Steven Spielberg', 'A.I.', 'director' ),
                          ('Hilary Swank', 'Million Dollar Baby', 'cast'),
                          ('Clint Eastwood', 'Million Dollar Baby', 'cast'),
                          ('Clint Eastwood', 'Million Dollar Baby', 'director'),
                          ('Clint Eastwood', 'Dirty Harry', 'cast')


select director.person_name 
from @film director 
full outer join @film actor 
    on director.person_name = actor.Person_name
    and director.film_title= actor.film_title
where actor.relationship = 'cast'
and director.relationship = 'director'
and actor.person_name is not null
and director.person_name is not null

fork query

created nov 2 11 conradfrix