Optimising SQL Query With Multiple Joins

Question

Hello,

I have the following three tables:

Venues, Events, Instance.

Events have a Venue (Venues have many events), and Events have many Instances.

For example, the Event "Terminator2" is a film that has a certain cinema as its Venue and will have many instances of that film (date times).

I need to run a query, which gives me a lost of Venues that satisfies the following:

Venue location is x. Venue has Events of a certain type. Thos Events have Instances within a certain time period.

I have achieved the first two with a JOIN on venue and Event (Events have a foreign key for the venue) and a WHERE clause to make sure the events are of a certain type. Now I need to make sure that the Events ALSO have Instances within a certain time period.

Do I add another JOIN on the Instances table (the Instances have a foreign key for an event) and another WHERE clause there?

What kind of performance should I expect from a double join, double where query like this?

Are there any suggestions as to doing this a better way?

Answer 1

SELECT v.Venue, e.Event, i.Instance
FROM Venue v
INNER JOIN Event e
ON v.EventID = e.EventID
INNER JOIN Instance i
ON e.EventID = i.EventID
WHERE v.Venue = 'X'
AND e.Event = 'Terminator2'
AND i.InstanceDateTime BETWEEN '11/01/2009' AND '11/01/2009 23:59:00'

Or you can include your criteria in the joins which may perform better, in some cases.

SELECT v.Venue, e.Event, i.Instance
FROM Venue v
INNER JOIN Event e
ON v.EventID = e.EventID
AND e.Event = 'Terminator2'
INNER JOIN Instance i
ON e.EventID = i.EventID
AND i.InstanceDateTime BETWEEN '11/01/2009' AND '11/01/2009 23:59:00'
WHERE v.Venue = 'X'

Answer 2

Sounds like you want an EXISTS clause:

select * from venues v
where v.venue_type = 'X'
and exists (select null
            from   events e
            join   instances i on i.event_id = e.event_id
            where  e.venue_id = v.venue_id
            and    e.event_type = 'Y'
            and    i.instance_date between :date1 and :date2
           )