Geek asks and answers

Oracle SQL - is there a standard, HOW EVERY workaround?

I am having trouble figuring out a way to lack Oracle support for the HAVING EVERY clause.

I have two tables: "Production and film" with the following diagram:

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

where "pid" is the integer value of the publisher ID, "middle" is an integer representing the identifier of the film, and the director is the name of the director of the film.

My goal is to get a list of publishers (by ID) that publish only films directed by Peter Jackson or Ben Affleck.

To achieve this, I wrote 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 does not 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

, , , WHERE.

- - ? -, ""? (, , ), Oracle ?

+5
4

:

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

sqlfiddle

+5

, -, , , , , A.B.Cade :

select distinct P.pid
    from Production P
    where P.pid not in (
        -- Get publishers that have produced a movie directed by someone else
        select P1.pid
        from Production P1 INNER JOIN Movie M ON P1.mid = M.mid
        where M.director not in ('Ben Affleck', 'Peter Jackson')
    )

SQLFiddle

, , , .

+3

, , , , :

SELECT DISTINCT P.pid
FROM Production P
LEFT JOIN (
    SELECT P1.pid
    FROM Production P1
    INNER JOIN Movie M ON (P1.mid = M.mid)
    WHERE M.director NOT IN ('Ben Affleck', 'Peter Jackson')
) V ON (P.pid = V.pid)
WHERE v.pid IS NULL;

- SQL Fiddle

+2

99:

SELECT 
    P.pid
FROM 
(
    SELECT DISTINCT 
        Pi.pid, M.Director
    FROM Production Pi 
    JOIN Movie M ON Pi.mid = M.mid
) P
GROUP BY 
    P.pid
HAVING 
    COUNT(p.Director) = 2 -- The directors should be exactly 2
    AND MIN(CASE WHEN p.Director in ('Ben Affleck', 'Peter Jackson') 
            THEN 1 ELSE 0 END) = 1

: http://www.anicehumble.com/2019/04/not-every-rdbms-has-every.html

, not in . , , .

with production as
(
   select *
   from (values 
      ('DC', 'Batman', 'Ben Affleck'),
      ('DC', 'Robin', 'Peter Jackson'),
      ('Not DC', 'Not Batman', 'Not Ben Affleck'),
      ('Not DC', 'Not Robin', 'Not Peter Jackson'),         
      ('Marvel', 'Avengers', 'Joe Russo'),
      ('WingNut', 'King Kong', 'Peter Jackson'),
      ('Century Fox', 'Deadpool', 'Ben Affleck'),
      ('Century Fox', 'Fantastic 4', 'Peter Jackson'),
      ('Century Fox', 'X-Men', 'Peter Jackson'),
      ('Millenium Fox', 'Scorpion', 'Ben Affleck'),
      ('Millenium Fox', 'Sub-Zero', 'Peter Jackson'),
      ('Millenium Fox', 'Liu Kang', 'Ed Boon')          
   ) as x(publisher, movie, director)
)
select distinct P.publisher
from production P
where P.publisher not in (
      -- Get publishers that have produced a movie directed by someone else
      select P1.publisher
      from production P1
      where P1.director not in ('Ben Affleck', 'Peter Jackson')
    )
;

. WingNut , .

| publisher   |
| ----------- |
| WingNut     |
| Century Fox |
| DC          |

, min(when true then 1 else 0) = 1

with production as
(
   select *
   from (values 
      ('DC', 'Batman', 'Ben Affleck'),
      ('DC', 'Robin', 'Peter Jackson'),
      ('Not DC', 'Not Batman', 'Not Ben Affleck'),
      ('Not DC', 'Not Robin', 'Not Peter Jackson'),         
      ('Marvel', 'Avengers', 'Joe Russo'),
      ('WingNut', 'King Kong', 'Peter Jackson'),
      ('Century Fox', 'Deadpool', 'Ben Affleck'),
      ('Century Fox', 'Fantastic 4', 'Peter Jackson'),
      ('Century Fox', 'X-Men', 'Peter Jackson'),
      ('Millenium Fox', 'Scorpion', 'Ben Affleck'),
      ('Millenium Fox', 'Sub-Zero', 'Peter Jackson'),
      ('Millenium Fox', 'Liu Kang', 'Ed Boon')          
   ) as x(publisher, movie, director)
)
select P.publisher
from (select distinct publisher, director from production) P
group by
    P.publisher
having
     count(p.Director) = 2 -- The directors should be exactly 2

     and min(case when p.Director in ('Ben Affleck', 'Peter Jackson') 
             then 1 else 0 end) = 1
;

Correct conclusion, only DC and Century Fox should be displayed. Because they are the only publishers that exclusively Ben Affleck and Peter Jackson work for.

| publisher   |
| ----------- |
| Century Fox |
| DC          |

Live test: https://www.db-fiddle.com/f/aDDw4Pd1DJzs6J5HgbKbdh/4

0
source

More articles:


All Articles