Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Unanswered: UDB SQL Outer Join Question

    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?
    Last edited by gretzkfan; 11-04-11 at 17:38.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Do not mix implicit and explicit join syntax, it will be easier to see where you go wrong.

  3. #3
    Join Date
    Nov 2011
    Posts
    2
    How would you explicitely join these tables?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by gretzkfan View Post
    How would you explicitely join these tables?
    May be the way that follows the logic of your requirements:

    Code:
    Event
    left outer join
    RuleLimit
     on RuleLimit.City = Event.City and RuleLimit.Allow = 'Yes'
    inner join
    RuleDetail
     on RuleDetail****leName = RuleLimit****leName
    etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •