Alias scoping in SQL

Question

I'm having an issue with a complex query on an SQLite3 database that I think has to do with a misunderstanding on my part of how to refer to columns in a results table returned by a select statement, especially when aliases are involved.

Here is an example table - a list of movie IDs with a row for each actor working on the movie:

CREATE TABLE movie_actor (imdb_id TEXT, actor TEXT);
INSERT INTO movie_actor VALUES('44r4', 'John Doe');
INSERT INTO movie_actor VALUES('44r4', 'Jane Doe');
INSERT INTO movie_actor VALUES('44r4', 'Jermaine Doe');
INSERT INTO movie_actor VALUES('44r4', 'Jacob Doe');
INSERT INTO movie_actor VALUES('55r5', 'John Doe');
INSERT INTO movie_actor VALUES('55r5', 'Jane Doe');
INSERT INTO movie_actor VALUES('55r5', 'Nathan Deer');
INSERT INTO movie_actor VALUES('66r6', 'Bob Duck');
INSERT INTO movie_actor VALUES('66r6', 'John Doe');
INSERT INTO movie_actor VALUES('66r6', 'Jermaine Doe');
INSERT INTO movie_actor VALUES('66r6', 'Jane Doe');
INSERT INTO movie_actor VALUES('77r7', 'John Doe');

I am trying to find out the how many times each pair of actors worked with each other across all movies. I decided to go about this with a self-join, but ran into issues where I would get record pairs such as "John Doe, Jane Doe, 3" and "Jane Doe, John Doe, 3" - this is really the same thing, and I wanted to only count the first version. This is the code that resulted:

SELECT DISTINCT 
    CASE WHEN d.actor_1 > d.actor_2 THEN d.actor_1 ELSE d.actor_2 END d.actor_1, 
    CASE WHEN d.actor_2 > d.actor_1 THEN d.actor_2 ELSE d.actor_1 END d.actor_2, 
    d.v 
FROM ( 
    SELECT c.actor_1 AS actor_1, c.actor_2 AS actor_2, COUNT(*) AS v 
    FROM ( 
        SELECT a.actor AS actor_1, b.actor AS actor_2 
        FROM movie_actor a JOIN movie_actor b ON a.imdb_id=b.imdb_id 
        ) AS c 
    WHERE c.actor_1 <> c.actor_2 
    GROUP BY c.actor_1, c.actor_2 
    HAVING COUNT(*) > 2 
    ORDER BY COUNT(*) DESC 
    LIMIT 20 
    ) 
    AS d

This doesn't run, but I can't figure out why. My assumption is that I am not using aliases properly, but I really don't know. Any ideas?

(SQL Fiddle link here)

Answer 1

We get a simpler query, if we add the condition a.actor < b.actor. This excludes pairs with equal actors and at the same time it removed the need of swapping actors.

SELECT
    a.actor AS actor_1, b.actor AS actor_2, COUNT(*) AS v
FROM
    movie_actor a
    INNER JOIN movie_actor b
        ON a.imdb_id = b.imdb_id
WHERE
    a.actor < b.actor
GROUP BY a.actor, b.actor
ORDER BY COUNT(*) DESC, a.actor, b.actor 
LIMIT 20 

Note: SQL always creates a cross product when joining, i.e. it creates all possible combinations of records that match the join condition. Therefore for imdb 55r5 (including 3 actors) it will first generate the following 3 x 3 = 9 pairs:

John Doe      John Doe
John Doe      Jane Doe
John Doe      Nathan Deer
Jane Doe      John Doe
Jane Doe      Jane Doe
Jane Doe      Nathan Deer
Nathan Deer   John Doe
Nathan Deer   Jane Doe
Nathan Deer   Nathan Deer

Then the WHERE-clause excludes all a >= b pairs and we get

John Doe      Nathan Deer
Jane Doe      John Doe
Jane Doe      Nathan Deer

Answer 2

Generate the distinct pairs first, then count them.

select actor_1, actor_2, count(*)
from (select distinct a.imdb_id, a.actor as actor_1, b.actor as actor_2
      from movie_actor a
      inner join movie_actor b on a.imdb_id = b.imdb_id
      where a.actor < b.actor) x
group by actor_1, actor_2
order by actor_1, actor_2;

actor_1     actor_2     count(*)  
----------  ----------  ----------
Bob Duck    Jane Doe    1         
Bob Duck    Jermaine D  1         
Bob Duck    John Doe    1         
Jacob Doe   Jane Doe    1         
Jacob Doe   Jermaine D  1         
Jacob Doe   John Doe    1         
Jane Doe    Jermaine D  2         
Jane Doe    John Doe    3         
Jane Doe    Nathan Dee  1         
Jermaine D  John Doe    2         
John Doe    Nathan Dee  1