What is the best SQL query in the following result set

Suppose I have a simple movie Participation information table (characters, movies, the relationship between characters and movies) is as follows:

create table film
(
 person_name varchar(48) not null, 
 film_title varchar(128) not null,
 relation varchar(48) not null
);

-- {'Mel Gibson','Braveheart', ' director' }
-- {'Mel Gibson','Braveheart','cast' }
-- {'Steven Spielberg','AI','director' }
-- { 'Hilary Swank','Million Dollar Baby','cast' }
-- etc

The database and tables are not created or maintained by me, I just query information from them.

I need to make a set of names for the actors (acting in front of the camera) in each film they direct. For those who have directed at least one film they didn’t take action on, or those who didn’t direct anything In other words, the situation should not apply.

My query (as far as I can prove, produced the correct result set), look at it:

( 
 select person_name 
 from film 
 where relation ='director'
)
except 
(
 select person_name
 from 
 (
 (
 select person_name , film_title 
 from film 
 where relation ='director'
) 
 except 
 (
 select person_name, film_title 
 from film 
 where relation ='cast'
 )
) as director_behind_camera_for_film
)

I want to know if the query is reasonable, or am I thinking about it all the time? If it is the latter, would you please provide a better solution or explanation?

Don’t pay too much attention to the fact that I use strings everywhere (surrogate keys may have been used) – this is a simplified example, but it still proves my challenge.

div>

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

SELECT tmp.person_name FROM
(
 SELECT person_name, film_title, COUNT(DISTINCT(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

I will say in advance, for your time And the respect of the website-this is a homework. However, I have considered and wrote a solution, but since I cannot successfully analyze it or obtain relevant third-party opinions elsewhere to determine if it is practical The above is a good fact, I am a little annoyed.

Suppose I have a simple movie participation information table (characters, movies, relations between characters and movies) as follows:

create table film
(
 person_name varchar(48) not null,
 film_title varchar(128) not null,
 relation varchar(48) not null
);

-- {'Mel Gibson','Braveheart ','director' }
-- {'Mel Gibson','Braveheart','cast' }
-- {'Steven Spielberg','AI','director' }
 - {'Hilary Swank','Million Dollar Baby','cast' }
-- etc

The database and tables are not created or maintained by me, I just look for information from them.< /p>

I need to make a set of names for the actors (acting in front of the camera) in each film they direct. For those who have directed at least one film they did not take action on, or those who did not direct any For the people of the matter, the situation should not apply.

My query (as far as I can prove, produced the correct result set), look at it:

< pre>(
select person_name
from film
where relation =’director’
)
except
(
select person_name
from
(
(
select person_name, film_title
from film
where relation =’director’
)
except
(
select person_name, film_title
from film
where relation = ‘cast’
)
) as director_behind_camera_for_film
)

I want to know if the query is reasonable, or am I thinking about it all the time? If it is the latter, would you please provide a better solution or explanation?

Don’t pay too much attention to the fact that I use strings everywhere (surrogate keys may have been used) – this is a simplified example, but it still proves my challenge.

p>

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

or

SELECT tmp.person_name FROM
(
 SELECT person_name, film_title, COUNT(DISTINCT(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