Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    5

    Unanswered: Outer Join Multiple Tables Question

    I am trying to join 3 tables - the third table is outer joined based on the first 2 tables.

    Select t1.id, t2.id,t3.id,t3.timestamp
    from table1 t1
    , table2 t2
    left outer join
    table3 t3 on ( t2.id = t3.id
    and t3.timestamp between t1.create_ts and t1.end_ts)
    where t1.id = t2.id

    theoretically - I can get a result as long as t1.id = t2.id. The outer join would provide a t3.
    However - when I am adding the t1 into the outer join I am getting an error

    [IBM][CLI Driver][DB2/6000] SQL0338N An ON clause associated with a JOIN operator or in a MERGE statement is not valid. SQLSTATE=42972

    Database Error #-338

    This join does not allow the t1.create_ts and t1.end_ts into the outer join "on" clause. Is there a way around this?
    I can use a temp table I guess ........................

  2. #2
    Join Date
    Jul 2009
    Posts
    10
    Select t1.id, t2.id,t3.id,t3.timestamp
    from table1 t1
    inner join
    table2 t2
    on t1.id = t2.id
    left outer join
    table3 t3 on ( t2.id = t3.id
    and t3.timestamp between t1.create_ts and t1.end_ts)

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    How about:
    Select t4.id1, t4.id2,t3.id,t3.timestamp
    from (
    Select t1.id as id1, t2.id as id2, t1.create_ts, t1.end_ts
    from table1 t1
    , table2 t2
    where t1.id = t2.id) as t4
    left outer join
    table3 t3 on ( t4.id2 = t3.id
    and t3.timestamp between t4.create_ts and t4.end_ts)
    Dave

  4. #4
    Join Date
    May 2009
    Posts
    5
    Thank you both ! I guess the sample I gave is oversimplified. But these both give me ideas on structuring thsi query and future queries. Mike.

  5. #5
    Join Date
    Jul 2009
    Posts
    150
    With bellow query we can get a different result:

    Select t1.id, t2.id,t3.id,t3.timestamp
    from
    table1 t1
    join
    table2 t2
    on t1.id = t2.id
    left join
    table3 t3 on
    t2.id = t3.id
    Where
    t3.timestamp between t1.create_ts and t1.end_ts
    __________________

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    DB2Plus,
    With the change you have offered you are changing the outer join back to an inner join. Try taking a look at a recent post by Robert Catteral and the articles by TerrY Purcell that Robert references.
    Dave

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although, it is not good practice to mix traditional join syntax(comma in from clause and join condition in where clause) and modern join syntax,
    this is another way just from my curiosity.

    Select t1.id, t2.id,t3.id,t3.timestamp
    from table1 t1
    left outer join
    table3 t3 on ( t1.id = t3.id
    and t3.timestamp between t1.create_ts and t1.end_ts)
    , table2 t2
    where t1.id = t2.id

Posting Permissions

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