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

-- sample for q 7973559
-- 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 tmp.person_name
FROM
(
   SELECT person_name, film_title, COUNT(relationship) as cnt
   FROM @film
   WHERE relationship IN ('cast', 'director')
   GROUP BY person_name, film_title
) as tmp
GROUP BY person_name
HAVING SUM(cnt) = COUNT(cnt)*2

fork query

created nov 2 11 conradfrix