Checking set equality-CodePudding

How to write a SQL query to list the film_id and title of every Film which has exactly the same cast acting in it as any other Film?

Database schema: enter image description here

My approach:

SELECT R.film_id, S.title
  FROM Film R, Film S 
 WHERE NOT EXISTS ( SELECT actor_id
                      FROM Film_Actor T
                     WHERE T.film_id = R.film_id 
                   EXCEPT
                    SELECT actor_id
                      FROM Film_Actor T
                     WHERE T.film_id = S.film_id )
       AND NOT EXISTS ( SELECT actor_id
                          FROM Film_Actor T
                         WHERE T.film_id = S.film_id 
                   EXCEPT
                        SELECT actor_id
                          FROM Film_Actor T
                         WHERE T.film_id = R.film_id )

Output: enter image description here

Expected output: enter image description here

CodePudding user response：

You can find all pairs of films with the exact same actors with the query:

select a.film_id, b.film_id
from film_actor a
full join film_actor b on a.film_id < b.film_id and a.actor_id = b.actor_id
group by a.film_id, b.film_id
having count(a.actor_id) = count(*) and count(b.actor_id) = count(*)

The query produces the ids of the films. With them, it's simple to get the names of the films.

CodePudding user response：

Your code is almost there, I have slightly updated it with some small changes and it should work. Basically the reason why you have the film_id and title swapped is because you are selecting the film_id from R, but title from S. It also wasn't filtered, so I added another condition the the Except.

SELECT DISTINCT R.film_id, R.title
FROM Film R, Film S 
WHERE NOT EXISTS ( SELECT actor_id
                  FROM Film_Actor T
                 WHERE T.film_id = R.film_id 
               EXCEPT
                SELECT actor_id
                  FROM Film_Actor T
                 WHERE T.film_id = S.film_id )
   AND NOT EXISTS ( SELECT actor_id
                      FROM Film_Actor T
                     WHERE T.film_id = S.film_id
               EXCEPT
                    SELECT actor_id
                      FROM Film_Actor T
                     WHERE T.film_id = R.film_id AND S.film_id != R.film_id)