SQL WHERE both values of a variable exist

user_14306904 0
Viewed 54

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.

select sql
2 Answers

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;
user_24227760

It seems filmname and country is the unique key for your table, i.e. there is one row per film and country.

One way to get films that sell better in country A than B is to aggregate and compare the earnings in the HAVING clause:

select filmname 
from boxoffice 
group by filmname
having max(case when country = 'A' then earnings end) >
       max(case when country = 'B' then earnings end)
order by filmname;

Another way is to join, e.g.:

select a.filmname
from (select * from boxoffice where country = 'A') a
join (select * from boxoffice where country = 'B') b
  on a.filmname = b.filmname and a.earnings > b.earnings
order by a.filmname;
user_22707620
Related