Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2007
    Posts
    34

    Unanswered: Joining 3 tables

    TABLE X
    -------------------------------------------
    COL SG COL VOL COL DB
    -------- -------- --------
    SG1 V1 T1
    SG1 V2 T1
    SG1 V3 T1
    SG2 V4 T1
    SG3 V5 T1
    SG3 V6 T1


    TABLE Y
    -------------------------------------------
    COL VOL COL SIZE1 COL DB
    -------- -------- --------
    V1 30 T1
    V2 20 T1
    V3 60 T1
    V4 10 T1
    V5 80 T1
    V6 40 T1


    TABLE Z
    ----------------------------------------------------------------
    COL SIZE2 COL OBJ COl DB COL VOL
    -------- -------- -------- --------
    23 TS1 T1 V1
    300 TS2 T1 V1
    4 TS3 T1 V1
    5 IX6 T1 V2
    14 IX7 T1 V4
    90 TS9 T1 V5


    Need a query which results in
    Z.OBJ values TS2 and TS9 .

    TS2 and TS9 of Z.OBJ have a size Z.SIZE2 > size of Y.SIZE1 of any X.VOL
    belonging to X.SG to which Z.OBJ belongs .

    Remarks :
    Z.VOL V1 belongs to X.SG1 which also has X.VOL V1, V2 and V3 so V1, V2 and V3 are good candidates in this case .
    X.SG2 candidate is only V4
    X.SG3 candidates are V5 and V6 .

    Z.SIZE2 has the same format as Y.SIZE1 .

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I don't understand what you want to do.

    A 3-way join is quite simple: First you join only two tables. The result of that join is a (temporary) table. Since you have a table, you can join this with another table. Let's have a look at this:
    Code:
    SELECT *
    FROM   t1
              JOIN
           t2 ON ( t1.id = t2.id )
              JOIN
           t3 ON ( t1.id = t3.id )
    To clarify this further, you could write it as:
    Code:
    SELECT *
    FROM   ( t1
                JOIN
             t2 ON ( t1.id = t2.id ) ) AS join_tab
              JOIN
           t3 ON ( join_tab.id = t3.id )
    The temporary table "join_tab" is the result of the join operation of "t1" and "t2". The second join step combines table "join_tab" with "t3".

    Essentially, you have to figure out which row(s) in X belongs to which row(s) in Y - the condition describing this will be the join predicate for the join of X and Y. The same has to be done for X and Z and Y and Z. It may happen that you do not have to apply a join predicate on one of those three combinations.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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