Looking for the best way to build a query in DB2(UDB) that will join three tables (actually more but 3 here for simplicity). Seems easy enough except that the make up of the tables do not lend themselve to a simple join. The details of the tables are as follows:

Event table

EventDate City
----------- -----------------------
12/12/2011 Dallas
12/20/2011 Houston
12/20/2011 Las Vegas
1/15/2012 Phoenix
1/15/2012 San Antonio
2/15/2012 San Diego

RuleDetail table

RuleName Start End ReserveSeats VIPSeats
--------- ----- ------- ------------- ------------
Platinum 12/1/2011 12/15/2011 3000 200
Gold 12/16/2011 12/31/2011 2000 100
Silver 1/1/2011 1/31/2012 1000 50
Bronze 2/1/2011 2/28/2012 500 25

RuleLimit table

RuleName City Allow
--------- ----------- -----
Platinum Dallas Yes
Gold Houston No
Gold Las Vegas No
Silver San Antonio No
Silver Los Angeles No

We want to know which rules are being utlized, including the rule details (RuleDetail table): Rulename, Start, End, Rerve Seats, VIP Seats. The issue lies in the make up of the RuleLimit table and the meaning of the Allow column. The RuleLimit rows are optional, a Rule does not need an entry in the RuleLimit table to be valid and used. When Allow = Yes an Event city must match the Limit city. When Allow = No the rule does not apply to that Event City.

I started with a query similar to this:

Select Distinct
RuleName, Start, End, ReserveSeats, VIPSeats
From
Event, RuleDetail LEFT OUTER JOIN RuleLimit ON (RuleDetail****leName = RuleLimit****leName)
Where
(Event.EventDate >= RuleDetail.Start and Event.EventDate <= RuleDetail.End) and
((RuleLimit.City is Null) or (RuleLimit.City = Event.City and Allow = 'Yes') or (RuleLimit.City <> Event.City and Allow = 'No'))

Results:
Platinum,12/1/2011,12/15/2011,3000,200
Gold,12/16/2011,2/31/2011,2000,100
Silver,1/1/2011,1/31/2012,1000,50
Bronze,2/1/2011,2/28/2012,500,25

There is probably a better way to do this but it worked ok except that it returned RuleName Gold as being utilized when in should not have. Only Houston and Las Vegas fit the timeline and they were both Allow='No'. It found it because Event.City (Houston) <> RuleLimit.City (Las Vegas) and Allow = 'No'.

What is the best way to build this query to limit the results correctly?

Do not mix implicit and explicit join syntax, it will be easier to see where you go wrong.

How would you explicitely join these tables?

May be the way that follows the logic of your requirements:

etc.