trjhtr

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`?

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.