SQL SELECT: I need a "IN ALL" Clause

Question

I have two tables. And I need all organizer_ids that organized ALL events between 2010 and 2012.

event
--------- 
event_id
name
year


event_organizer
---------
event_id
organizer_id

I tried something like this:

SELECT organizer_id 
  FROM event_organizer
 WHERE event_id IN (SELECT event_id 
                      FROM event 
                     WHERE year>=2010 AND year<=2012);

But it didn't work because I need only the organizer_id which have organized ALL events between 2010 and 2012. I have no idea how the query should look like that it works as I want.

Answer 1

You can write:

SELECT t1.organizer_id
  FROM ( SELECT eo.organizer_id,
                COUNT(1) AS num_events
           FROM event_organizer eo
           JOIN event e
             ON eo.event_id = e.event_id
          WHERE e.year BETWEEN 2010 AND 2012
          GROUP
             BY eo.organizer_id
       ) t1
  JOIN ( SELECT COUNT(1) AS num_events
           FROM event e
          WHERE e.year BETWEEN 2010 AND 2012
       ) t2
    ON t1.num_events = t2.num_events
;

(The idea for the above is: for each organizer, find out how many events (s)he organized between 2010 and 2012. Then, compare this to the total number of events between 2010 and 2012. If these two numbers are the same, then the organizer organized all events in that time period.)

Alternatively, you can write:

SELECT organizer_id
  FROM organizer       -- I assume you have an 'organizer' table?
 WHERE organizer_id NOT IN
        ( SELECT o.organizer_id
            FROM event e
           CROSS
            JOIN organizer o
            LEFT
           OUTER
            JOIN event_organizer eo
              ON e.event_id = eo.event_id
             AND o.organizer_id = eo.organizer_id
           WHERE e.year BETWEEN 2010 and 2012
             AND eo.event_id IS NULL
        )
;

(The idea for this one is: find all event–organizer pairings such that the organizer did not organize the event. Then, return all organizers who don't appear in any such pairings.)