There are three simple tables. Actor, Film, Film_actor.

Film_Actor table:

 actor_id     | film_id
     1        |  3
     2        |  4

Actor table:

 actor_id   | first_name                  | last_name
     1      | Daniel                      |  Craig
     2      | Eva                         |  Green

Film table:

 film_id     | title                      
    3        | Casino Royale
    4        | Colombo

I would like to find two actors who cast together the most and list the titles of those movies. Moreover, actor_id of the first_actor should be lower than actor_id of the second_actor.

So the desired result should look like this:

first_actor     | second_actor  | title
Daniel Craig    | Eva Green     | Casino Royale

I've written the following code:

        ac1.actor_id first_actor_id
        , ac1.first_name + ' ' + ac1.last_name   first_actor
        , ac2.actor_id second_actor_id
        , ac2.first_name + ' ' + ac2.last_name second_actor
        ,count(*) FilmCount
        Actor as ac1 
        INNER JOIN Film_Actor as fa1 ON fa1.actor_id = ac1.actor_id
        INNER JOIN Film_Actor as fa2 ON fa1.film_id = fa2.film_id
        INNER JOIN Actor as ac2 ON fa2.actor_id = ac2.actor_id 
            and fa1.actor_id < ac2.actor_id
        INNER JOIN Film as fi ON fa1.film_id = fi.film_id
        , ac1.first_name
        , ac1.last_name   
        , ac2.actor_id
        , ac2.first_name
        , ac2.last_name

In my view, the above code is a little bit clumsy as the code makes checking of this condition actor_id of the first_actor should be lower than actor_id of the second_actor*` just for second actor.

Is it possible to improve comparing actor_id of the first_actor with actor_id of the second_actor`?


Since inner joins are used, you'll only ever be returned rows where the first_actor_id is smaller than the second_actor_id, so the code works as intended.

If I understand correctly, you want the code to check that first_actor_id is smaller than second_actor_id AND second_actor_id is bigger than first_actor_id. However, those two conditions are the same so you only need one of them.

As a sidenote, your code will not work as intended for the following reasons: (1) It will bring back any actors who have starred in ONE movie together, rather than two, and (2) it does not list the movie names they have starred in.

The first problem is easy to fix; add HAVING count(*) >= 2 to the end of the statement.

The second problem depends on the version of SQL Server you are using (use SELECT @@VERSION to find out):

  • If you're running SQL Server 2017 or later, exchange count(*) in the SELECT for STRING_AGG(fi.title, ', ') (documentation linked) – this will give you a comma-separated list of films.
  • If not, the best way to concatenate strings from a group is the "stuff XML path" method. A quick search online should explain how this trick works.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.