Oracle SQL - is there a standard HAVING EVERY workaround?

Question

I am having trouble figuring out a way around Oracle's lack of support for the HAVING EVERY clause.

I have two tables, Production and Movie, with the following schema:

Production (pid, mid)
Movie(mid, director)

where 'pid' is in integer representing publisher ID, 'mid' is an integer representing movie ID, and director is the name of the movie's director.

My goal is to get a list of publishers (by ID) which have only published movies directed by Peter Jackson or Ben Affleck.

In order to achieve this, I had written the following query:

SELECT *
    FROM Production P, Movie M
    WHERE P.mid = M.mid;
    GROUP BY P.pid
    HAVING EVERY ( M.director IN ('Ben Affleck', 'Peter Jackson') );

But since Oracle doesn't support HAVING EVERY, all I get is the following error:

    HAVING EVERY ( M.director IN ('ben affleck', 'PJ') )
                          *
ERROR at line 5:
ORA-00907: missing right parenthesis

Because the directorship has to apply to every movie produced by the publisher, I don't believe the condition can be moved to the WHERE clause.

Is there any way around this roadblock? Anything that's considered "standard"? Also (and perhaps more importantly) why did Oracle choose not to implement HAVING EVERY?

Answer 1

Try this:

SELECT P.pid
FROM (select distinct Pi.pid, M.Director
      from Production Pi INNER JOIN 
    Movie M ON Pi.mid = M.mid) P
GROUP BY P.pid
HAVING sum(case when P.Director in ('Ben Affleck', 'Peter Jackson') 
           then 1 else 99 end) = 2

Here is a sqlfiddle demo