SQL WHERE both values of a variable exist

Question

Suppose that I have a table of global box office information including columns "filmName", "country" and "earnings". The question is how to find out the films that sell better in country A than in country B. Here is my answer:

SELECT filmName 
FROM Boxoffice 
WHERE (SELECT earnings FROM Boxoffice WHERE country = "A") > 
(SELECT earnings FROM Boxoffice WHERE country = "B") 
GROUP BY filmName

But then I found out that there are some films that are not shown in both countries. I wonder how I can add the condition to the films that are shown in both countries to my existed answer. And I also have no idea if my answer has any problem since I do not have the real data.

Answer 1

I think a self join would be simpler:

SELECT a.filmname
FROM   boxoffice a
JOIN   boxoffice b ON a.country = 'A' AND b.country = 'B' AND a.earnings > b.earnings;