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:
12/20/2011 Las Vegas
1/15/2012 San Antonio
2/15/2012 San Diego
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:
RuleName, Start, End, ReserveSeats, VIPSeats
Event, RuleDetail LEFT OUTER JOIN RuleLimit ON (RuleDetail****leName = RuleLimit****leName)
(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'))
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?