# Comparing columns values and change their places by condition

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.
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.