Comparing columns values and change their places by condition

Question

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:

SELECT   
        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
    FROM
        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
    GROUP BY 
        ac1.actor_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`?

Answer 1

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.