SQL SELECT: I need a "ALL ALL" clause

Question

I have two tables. And I need all the organizer_id that organized ALL the events from 2010 to 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 this did not work, because I only need organizer_id , which organized ALL events between 2010 and 2012. I have no idea what the query should look like, how it works, how 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 above: for each organizer, find out how many events (s) he organized in the period from 2010 to 2012. Then compare this with the total number of events in the period from 2010 to 2012. If these two numbers are the same, then the organizer organized all events for this 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 is to: find all pairs of events and organizer guys so that the organizer does not organize the event. Then return all organizers that do not appear in such pairings.)

Answer 2

I think the easiest way to make such a request is with aggregation:

 with events as ( select event_id FROM event WHERE year>=2010 AND year<=2012 ) select organizer_id from event_organizer eo where event_id in (select event_id from events) group by organizer_id having count(distinct event_id) = (select count(*) from events)