Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2009
    Posts
    10

    Unanswered: SQL Query Help Please--

    Hello All,
    I need help with following two different situations:

    assume TBLM is Master table and TBLA and TBLB are child tables.

    1) Two tables - TBLA AND TBLB

    COL1and COL2 are keys on both tables.

    I would like to check the existense of a row..with COL1 and COL2 as KEY
    a) Present in TBLA?
    b) Present in TBLB
    c) Present in TBLA and TBLB
    d) NOT present in either of tables - TBLA or TBLB

    Am doing query like below..not sure..if it is right..

    SELECT 1 from
    TBLA A
    where COL1 = ----
    AND COL2 = ----
    AND NOT EXISTS (SELECT 1 FROM TBLB B

    where COL1 = ----
    AND COL2 = ----)


    Please advice..

    2) I have following query.........

    SELECT COALESCE (TBLA.KEY1, TBLB.KEY1) AS RES_KEY1
    ,COALESCE(TBLA.KEY2, TBLB.KEY2) AS RES_KEY2
    ,COALESCE(TBLA.KEY3, TBLB.KEY3) AS RES_KEY3
    ,COALESCE(TBLA.COL1, ' ') AS RES_VAL11
    ,COALESCE(TBLA.COL2, ' ') AS RES_VAL2
    ,COALESCE(TBLB.COL1, ' ') AS RES_VAL3
    ,COALESCE(TBLB.COL2, ' ') AS RES_VAL4

    FROM TBLA
    FULL OUTER JOIN TBLB


    ON TBLA.KEY1= TBLB.KEY1
    AND TBLA.KEY2= TBLB.KEY2
    AND TBLA.KEY3= TBLB.KEY3

    WHERE EXISTS (SELECT 1
    FROM TBLM
    WHERE TBLA.KEY1= TBLM.KEY1
    AND TBLA.KEY2= TBLM.KEY2)


    with ur;

    BUT..one more thing..I need to tie KEY1 AND KEY2 to check against a master table existence.

    Meaning if I get KEY1 VALUE from either TBLA or TBLB ..I need to check that values exists in master table (TBLM). TBLM has KEY1 and KEY2 that I need to check its existence for rows retrieved by the above query.

    Please advice ..how do I join TBLM to check the existense of KEY1 AND KEY2.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1) Two tables - TBLA AND TBLB

    COL1and COL2 are keys on both tables.

    I would like to check the existense of a row..with COL1 and COL2 as KEY
    a) Present in TBLA?
    b) Present in TBLB
    c) Present in TBLA and TBLB
    d) NOT present in either of tables - TBLA or TBLB
    I couldn't understand the condition d).
    Because if I only see TBLA and TBLB, how to know the keys which are not in both tables.

    1-1) Refer to TBLM to know the keys which are not in both TBLA and TBLB.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     tblm(col1, col2, colx) AS (VALUES
     (1, 1, 'M 1-1')
    ,(1, 2, 'M 1-2')
    ,(1, 3, 'M 1-3')
    ,(2, 1, 'M 2-1')
    )
    ,tbla(col1, col2, colx) AS (VALUES
     (1, 1, 'A 1-1 A and B')
    ,(1, 2, 'A 1-2 A only')
    ,(3, 1, 'A 3-1 Not in M')
    )
    ,tblb(col1, col2, colx) AS (VALUES
     (1, 1, 'B 1-1 A and B')
    ,(1, 3, 'B 1-3 B only')
    ,(4, 1, 'B 4-1 Not in M')
    )
    SELECT
           col1, col2
         , descm, desca, descb
         , SUBSTR('NY', a  +1, 1)   AS "Present in A"
         , SUBSTR('NY', b  +1, 1)   AS "Present in B"
         , SUBSTR('NY', a*b+1, 1)   AS "Present in A and B"
         , SUBSTR('NY', (1-a)*(1-b)+1, 1) AS "NOT Present in either of tables"
      FROM (SELECT
                   m.col1, m.col2
                 , m.colx AS descm
                 , a.colx AS desca
                 , b.colx AS descb
                 , CASE WHEN a.col1 IS NOT NULL THEN 1 ELSE 0 END AS a
                 , CASE WHEN b.col1 IS NOT NULL THEN 1 ELSE 0 END AS b
              FROM TBLM M
              LEFT JOIN
                   TBLA A
               ON  a.col1 = m.col1
               AND a.col2 = m.col2
              LEFT JOIN
                   TBLB B
               ON  b.col1 = m.col1
               AND b.col2 = m.col2
           ) Q
     ORDER BY
           col1, col2
    ;
    ------------------------------------------------------------------------------
    
    COL1        COL2        DESCM DESCA          DESCB          Present in A Present in B Present in A and B NOT Present in either of tables
    ----------- ----------- ----- -------------- -------------- ------------ ------------ ------------------ -------------------------------
              1           1 M 1-1 A 1-1 A and B  B 1-1 A and B  Y            Y            Y                  N                              
              1           2 M 1-2 A 1-2 A only   -              Y            N            N                  N                              
              1           3 M 1-3 -              B 1-3 B only   N            Y            N                  N                              
              2           1 M 2-1 -              -              N            N            N                  Y                              
    
      4 record(s) selected.
    1-2) Only see TBLA and TBLB
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     tblm(col1, col2, colx) AS (VALUES
     (1, 1, 'M 1-1')
    ,(1, 2, 'M 1-2')
    ,(1, 3, 'M 1-3')
    ,(2, 1, 'M 2-1')
    )
    ,tbla(col1, col2, colx) AS (VALUES
     (1, 1, 'A 1-1 A and B')
    ,(1, 2, 'A 1-2 A only')
    ,(3, 1, 'A 3-1 Not in M')
    )
    ,tblb(col1, col2, colx) AS (VALUES
     (1, 1, 'B 1-1 A and B')
    ,(1, 3, 'B 1-3 B only')
    ,(4, 1, 'B 4-1 Not in M')
    )
    SELECT
           col1, col2
         , desca, descb
         , SUBSTR('NY', a  +1, 1)   AS "Present in A"
         , SUBSTR('NY', b  +1, 1)   AS "Present in B"
         , SUBSTR('NY', a*b+1, 1)   AS "Present in A and B"
    --     , SUBSTR('NY', (1-a)*(1-b)+1, 1) AS "NOT Present in either of tables"
      FROM (SELECT
                   COALESCE(a.col1, b.col1) AS col1
                 , COALESCE(a.col2, b.col2) AS col2
                 , a.colx AS desca
                 , b.colx AS descb
                 , CASE WHEN a.col1 IS NOT NULL THEN 1 ELSE 0 END AS a
                 , CASE WHEN b.col1 IS NOT NULL THEN 1 ELSE 0 END AS b
              FROM
                   TBLA A
              FULL JOIN
                   TBLB B
               ON  b.col1 = a.col1
               AND b.col2 = a.col2
           ) Q
     ORDER BY
           col1, col2
    ;
    ------------------------------------------------------------------------------
    
    COL1        COL2        DESCA          DESCB          Present in A Present in B Present in A and B
    ----------- ----------- -------------- -------------- ------------ ------------ ------------------
              1           1 A 1-1 A and B  B 1-1 A and B  Y            Y            Y                 
              1           2 A 1-2 A only   -              Y            N            N                 
              1           3 -              B 1-3 B only   N            Y            N                 
              3           1 A 3-1 Not in M -              Y            N            N                 
              4           1 -              B 4-1 Not in M N            Y            N                 
    
      5 record(s) selected.
    2) I am not sure that I fully understand your requirements.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     tblm(key1, key2, col1) AS (VALUES
     (1, 1, 'M 1-1')
    ,(1, 2, 'M 1-2')
    ,(1, 3, 'M 1-3')
    ,(2, 3, 'M 2-3')
    ,(3, 1, 'M 3-1')
    ,(4, 1, 'M 4-1')
    )
    ,tbla(key1, key2, key3, col1) AS (VALUES
     (1, 1, 1, 'A 1-1-1 A B M')
    ,(1, 2, 1, 'A 1-2-1 A - M')
    ,(2, 1, 1, 'A 2-1-1 A B -')
    ,(2, 2, 1, 'A 2-2-1 A - -')
    ,(3, 1, 1, 'A 3-1-1 A - M')
    )
    ,tblb(key1, key2, key3, col1) AS (VALUES
     (1, 1, 1, 'B 1-1-1 A B M')
    ,(1, 2, 2, 'B 1-2-2 - B M')
    ,(2, 1, 1, 'B 2-1-1 A B -')
    ,(2, 2, 2, 'B 2-2-2 - B -')
    ,(4, 1, 1, 'B 4-1-1 - B M')
    )
    
    SELECT
           COALESCE(A.KEY1, B.KEY1) AS RES_KEY1
         , COALESCE(A.KEY2, B.KEY2) AS RES_KEY2
         , COALESCE(A.KEY3, B.KEY3) AS RES_KEY3
         , COALESCE(A.col1, '') AS RES_VALa
         , COALESCE(B.col1, '') AS RES_VALb
         , COALESCE(m.col1, '') AS res_valm
      FROM
           TBLA A
      FULL OUTER JOIN
           TBLB B
       ON  a.key1 = b.key1
       AND a.key2 = b.key2
       AND a.key3 = b.key3
      LEFT OUTER JOIN
           TBLM M
       ON  m.key1 = COALESCE(a.key1, b.key1)
       AND m.key2 = COALESCE(a.key2, b.key2)
     ORDER BY
           res_key1, res_key2;
    ------------------------------------------------------------------------------
    
    RES_KEY1    RES_KEY2    RES_KEY3    RES_VALA      RES_VALB      RES_VALM
    ----------- ----------- ----------- ------------- ------------- --------
              1           1           1 A 1-1-1 A B M B 1-1-1 A B M M 1-1   
              1           2           1 A 1-2-1 A - M               M 1-2   
              1           2           2               B 1-2-2 - B M M 1-2   
              2           1           1 A 2-1-1 A B - B 2-1-1 A B -         
              2           2           1 A 2-2-1 A - -                       
              2           2           2               B 2-2-2 - B -         
              3           1           1 A 3-1-1 A - M               M 3-1   
              4           1           1               B 4-1-1 - B M M 4-1   
    
      8 record(s) selected.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    2-2) Check existence of key1 and key2 independently.
    Note: Test data were not considered enough. There may be redundant or lack of cases.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     tblm(key1, key2, col1) AS (VALUES
     (1, 1, 'M 1-1')
    ,(1, 2, 'M 1-2')
    ,(1, 3, 'M 1-3')
    ,(2, 3, 'M 2-3')
    ,(3, 1, 'M 3-1')
    ,(4, 1, 'M 4-1')
    )
    ,tbla(key1, key2, key3, col1) AS (VALUES
     (1, 1, 1, 'A 1-1-1 A B M')
    ,(1, 2, 1, 'A 1-2-1 A - M')
    ,(2, 1, 1, 'A 2-1-1 A B -')
    ,(2, 2, 1, 'A 2-2-1 A - -')
    ,(3, 1, 1, 'A 3-1-1 A - M')
    ,(3, 4, 1, 'A 3-4-1 A - -')
    ,(5, 1, 1, 'A 5-1-1 A B -')
    ,(5, 4, 1, 'A 5-4-1 A - -')
    )
    ,tblb(key1, key2, key3, col1) AS (VALUES
     (1, 1, 1, 'B 1-1-1 A B M')
    ,(1, 2, 2, 'B 1-2-2 - B M')
    ,(2, 1, 1, 'B 2-1-1 A B -')
    ,(2, 2, 2, 'B 2-2-2 - B -')
    ,(4, 1, 1, 'B 4-1-1 - B M')
    ,(4, 4, 1, 'B 4-4-1 - B -')
    ,(5, 1, 1, 'B 5-1-1 A B -')
    ,(5, 5, 1, 'B 5-5-1 - B -')
    )
    
    SELECT
           COALESCE(A.KEY1, B.KEY1) AS RES_KEY1
         , COALESCE(A.KEY2, B.KEY2) AS RES_KEY2
         , COALESCE(A.KEY3, B.KEY3) AS RES_KEY3
         , COALESCE(A.col1, '') AS RES_VALa
         , COALESCE(B.col1, '') AS RES_VALb
         , COALESCE(m.col1, '') AS res_valm
         , CASE
           WHEN EXISTS
                (SELECT *
                   FROM TBLM M
                  WHERE m.key1 = COALESCE(a.key1, b.key1)
                )  THEN
                'Y'
           ELSE 'N'
           END  AS "Exists key1 in Master"
         , CASE
           WHEN EXISTS
                (SELECT *
                   FROM TBLM M
                  WHERE m.key2 = COALESCE(a.key2, b.key2)
                )  THEN
                'Y'
           ELSE 'N'
           END  AS "Exists key2 in Master"
      FROM
           TBLA A
      FULL OUTER JOIN
           TBLB B
       ON  a.key1 = b.key1
       AND a.key2 = b.key2
       AND a.key3 = b.key3
      LEFT OUTER JOIN
           TBLM M
       ON  m.key1 = COALESCE(a.key1, b.key1)
       AND m.key2 = COALESCE(a.key2, b.key2)
     ORDER BY
           res_key1, res_key2;
    ------------------------------------------------------------------------------
    
    RES_KEY1    RES_KEY2    RES_KEY3    RES_VALA      RES_VALB      RES_VALM Exists key1 in Master Exists key2 in Master
    ----------- ----------- ----------- ------------- ------------- -------- --------------------- ---------------------
              1           1           1 A 1-1-1 A B M B 1-1-1 A B M M 1-1    Y                     Y                    
              1           2           1 A 1-2-1 A - M               M 1-2    Y                     Y                    
              1           2           2               B 1-2-2 - B M M 1-2    Y                     Y                    
              2           1           1 A 2-1-1 A B - B 2-1-1 A B -          Y                     Y                    
              2           2           1 A 2-2-1 A - -                        Y                     Y                    
              2           2           2               B 2-2-2 - B -          Y                     Y                    
              3           1           1 A 3-1-1 A - M               M 3-1    Y                     Y                    
              3           4           1 A 3-4-1 A - -                        Y                     N                    
              4           1           1               B 4-1-1 - B M M 4-1    Y                     Y                    
              4           4           1               B 4-4-1 - B -          Y                     N                    
              5           1           1 A 5-1-1 A B - B 5-1-1 A B -          N                     Y                    
              5           4           1 A 5-4-1 A - -                        N                     N                    
              5           5           1               B 5-5-1 - B -          N                     N                    
    
      13 record(s) selected.
    Last edited by tonkuma; 03-21-09 at 10:30.

  4. #4
    Join Date
    Mar 2009
    Posts
    10
    Hello Tonkuma,

    Thank you for your feedback..

    For query#1 ..let me put in a different way what am looking for.

    1) Two tables - TBLA AND TBLB

    COL1and COL2 are keys on both tables.

    I would like to check the existense of a row..with COL1 and COL2 as KEY
    a) Present in TBLA?
    b) Present in TBLB
    c) Present in TBLA and TBLB

    I would like a single query which will tell me the existense of the row in either TBLA or TBLB OR SQLCODE=+100-if row is not present in either of the two tables.

    Query#2 - am looking at yor feedbback and trying to make it work. Will update on that shortly.

    Many Thanks for your time and effort please

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1) Two tables - TBLA AND TBLB

    COL1and COL2 are keys on both tables.

    I would like to check the existense of a row..with COL1 and COL2 as KEY
    a) Present in TBLA?
    b) Present in TBLB
    c) Present in TBLA and TBLB

    I would like a single query which will tell me the existense of the row in either TBLA or TBLB OR SQLCODE=+100-if row is not present in either of the two tables.
    Is this not enough?
    Note 1: These test data may be redundant as an example.
    (3, 1, 'A 3-1 Not in M')
    (4, 1, 'B 4-1 Not in M')
    Note 2: If you want to check existence for specific values of key1 and key2, you can add
    WHERE COALESCE(a.col1, b.col1) = :v_col1
    AND COALESCE(a.col2, b.col2) = :v_col2

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     tbla(col1, col2, colx) AS (VALUES
     (1, 1, 'A 1-1 A and B')
    ,(1, 2, 'A 1-2 A only')
    ,(3, 1, 'A 3-1 Not in M')
    )
    ,tblb(col1, col2, colx) AS (VALUES
     (1, 1, 'B 1-1 A and B')
    ,(1, 3, 'B 1-3 B only')
    ,(4, 1, 'B 4-1 Not in M')
    )
    SELECT
           COALESCE(a.col1, b.col1) AS col1
         , COALESCE(a.col2, b.col2) AS col2
         , a.colx                   AS desc_a
         , b.colx                   AS desc_b
      FROM
           TBLA A
      FULL JOIN
           TBLB B
       ON  b.col1 = a.col1
       AND b.col2 = a.col2
    /*
     WHERE COALESCE(a.col1, b.col1) = 1
       AND COALESCE(a.col2, b.col2) = 1
    */
     ORDER BY
           col1, col2
    ;
    ------------------------------------------------------------------------------
    
    COL1        COL2        DESC_A         DESC_B        
    ----------- ----------- -------------- --------------
              1           1 A 1-1 A and B  B 1-1 A and B 
              1           2 A 1-2 A only   -             
              1           3 -              B 1-3 B only  
              3           1 A 3-1 Not in M -             
              4           1 -              B 4-1 Not in M
    
      5 record(s) selected.

  6. #6
    Join Date
    Mar 2009
    Posts
    10
    1) Three tables - TBLA, TBLB --> Child Tables. TBLM - Master table.

    COL1and COL2 are keys on all three tables

    I would like to check the existense of a row..from TBLA and TBLB against TBLM.
    a) Present in TBLA, present in TBLM, Not in TBLB - say give 'A' in result table
    b) Present in TBLB, present in TBLM, Not in TBLA - say give 'B' in result table
    c) Present in TBLA, present in TBLB, present in TBLM - say give 'M' in result table

    I would like a single query which will tell me the existense of the row in either for above a), b), c) conditions OR SQLCODE=+100-if row is not present in either of the two tables against TBLM.


    I would just need to know by select 'A' from TBLA where A.COL1 = M.COL1 and A.COL2 = M.COL2 and NOT Exists (Select 'B' from ..TBLB where B.COL1 = M.COL1 and B.COL2 = M.COL2 .
    so that if row is either in tblA or in tblB and present in TBLM, get 'A' or 'B'

    if row is not there in TBLM ..then SQLCODE = +100 is expected ..
    Last edited by TopNotch; 03-22-09 at 23:33.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Your requirements would be summarized in the follwing table.

    Code:
    o: exist
    x: not exist
    
    M A B Result
    - - - ------
    o o o M
    o o x A
    o x o B
    o x x -100
    x o o -100
    x o x -100
    x x o -100
    Use TBLM LEFT OUTER JOIN TBLA ON ... LEFT OUTER JOIN TBLB ON ... and TBLA.COL1 IS NOT NULL to check existence of a row of TBLA in CASE expression in SELECT list.

    I showed some examples of OUTER JOIN and CASE expression.
    So I feel that it will be not so difficult to construct complete SELECT statement.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    For this condition
    o x x -100

    Add WHERE (TBLA.COL1 IS NOT NULL OR TBLB.COL1 IS NOT NULL).

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    o: exists
    x: not exists
    
    M A B Result
    - - - ------
    o o o M
    o o x A
    o x o B
    o x x -100
    x o o -100
    x o x -100
    x x o -100
    Another solution may be:
    TBLM INNER JOIN (TBLA FULL OUTER JOIN TBLB ON ...) ON ...

    Note: Parenthesis are not necessary.

  10. #10
    Join Date
    Mar 2009
    Posts
    10
    query would like:
    SELECT

    CASE WHEN a.col1 IS NOT NULL THEN 1 ELSE 0 END AS A
    , CASE WHEN b.col1 IS NOT NULL THEN 1 ELSE 0 END AS B
    FROM TBLM M
    LEFT JOIN
    TBLA A
    ON a.col1 = m.col1
    AND a.col2 = m.col2
    LEFT JOIN
    TBLB B
    ON b.col1 = m.col1
    AND b.col2 = m.col2

    Please advice..to get A, B, M in result table ..

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You want to get M, A or B in a result column.
    These will be got by a CASE expression.

    Code:
    SELECT
           CASE
           WHEN a.col1 IS NOT NULL
            AND b.col1 IS NOT NULL THEN 'M'
           WHEN a.col1 IS NOT NULL THEN 'A'
           WHEN b.col1 IS NOT NULL THEN 'B'
           END  AS result
    FROM
           TBLM M
    .....
    I want to recomend you to read through "CASE expressions" in the manual "DB2 SQL Reference Volume 2" or in the DB2 Information Center.

  12. #12
    Join Date
    Mar 2009
    Posts
    10
    I have following requirement:

    Three tables - TBLM, TBLA and TBLB.

    I have to get everything from TBLA and TBLB which have a record in TBLM(Master Table). TBLA and TBLB are child tables.

    SELECT COALESCE (TBLA.KEY1, TBLB.KEY1) AS RES_KEY1
    ,COALESCE(TBLA.KEY2, TBLB.KEY2) AS RES_KEY2
    ,COALESCE(TBLA.KEY3, TBLB.KEY3) AS RES_KEY3
    ,COALESCE(TBLA.COL1, ' ') AS RES_VAL11
    ,COALESCE(TBLA.COL2, ' ') AS RES_VAL2
    ,COALESCE(TBLB.COL1, ' ') AS RES_VAL3
    ,COALESCE(TBLB.COL2, ' ') AS RES_VAL4

    FROM TBLA
    FULL OUTER JOIN TBLB

    ON TBLA.KEY1= TBLB.KEY1
    AND TBLA.KEY2= TBLB.KEY2
    AND TBLA.KEY3= TBLB.KEY3

    with ur;



    Please advice ..how do I join TBLM to check the existense of KEY1 AND KEY2 ON TBLM?

  13. #13
    Join Date
    Mar 2009
    Posts
    10
    For the recent post I had, I had the following query working:

    SELECT COALESCE (TBLA.KEY1, TBLB.KEY1) AS RES_KEY1
    ,COALESCE(TBLA.KEY2, TBLB.KEY2) AS RES_KEY2
    ,COALESCE(TBLA.KEY3, TBLB.KEY3) AS RES_KEY3
    ,COALESCE(TBLA.COL1, ' ') AS RES_VAL11
    ,COALESCE(TBLA.COL2, ' ') AS RES_VAL2
    ,COALESCE(TBLB.COL1, ' ') AS RES_VAL3
    ,COALESCE(TBLB.COL2, ' ') AS RES_VAL4

    FROM TBLM
    ,TBLA
    FULL OUTER JOIN TBLB

    ON TBLA.KEY1= TBLB.KEY1
    AND TBLA.KEY2= TBLB.KEY2
    AND TBLA.KEY3= TBLB.KEY3

    WHERE TBLM.KEY1 = COALESCE(TBLA.KEY1, TBLB.KEY1)
    AND TBLM.KEY2 = COALESCE(TBLA.KEY2, TBLB.KEY2)

    with ur;
    I think the above one satisfies the JOIN on TBLM..

    Many thanks to Tonkuma...many thanks again..

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Your join clause
    FROM TBLM
    ,TBLA
    FULL OUTER JOIN TBLB

    ON TBLA.KEY1= TBLB.KEY1
    AND TBLA.KEY2= TBLB.KEY2
    AND TBLA.KEY3= TBLB.KEY3

    WHERE TBLM.KEY1 = COALESCE(TBLA.KEY1, TBLB.KEY1)
    AND TBLM.KEY2 = COALESCE(TBLA.KEY2, TBLB.KEY2)
    would result only rows which have corresponding rows in TBLM,
    because older join syntax (which was used "," in FROM clause and join conditions in WHERE clause)
    has the same meaning as the following INNER JOIN syntax which I was suggested before
    Another solution may be:
    TBLM INNER JOIN (TBLA FULL OUTER JOIN TBLB ON ...) ON ...

    Note: Parenthesis are not necessary.
    Code:
    FROM
           TBLM
     INNER JOIN
      ( -- parentheses are used only to clarify the syntax
           TBLA
      FULL OUTER JOIN
           TBLB
       ON  TBLA.KEY1= TBLB.KEY1
       AND TBLA.KEY2= TBLB.KEY2
       AND TBLA.KEY3= TBLB.KEY3
      ) -- parentheses are used only to clarify the syntax
       ON  TBLM.KEY1 = COALESCE(TBLA.KEY1, TBLB.KEY1)
       AND TBLM.KEY2 = COALESCE(TBLA.KEY2, TBLB.KEY2)
    Using my diagram showed sometimes before, that would be expressed as following:
    Code:
    M A B Result
    - - - ------
    o o o M
    o o x A
    o x o B
    o x x -100
    x o o -100
    x o x -100
    x x o -100
    I guessed that your requirement might be different from that and same as the followings diagram, accoding to your statement.
    Please advice ..how do I join TBLM to check the existense of KEY1 AND KEY2 ON TBLM?
    Code:
    M A B Result
    - - - ------
    o o o M
    o o x A
    o x o B
    o x x -100
    x o o c
    x o x a
    x x o b
     
    where I chosed arbitlary c, a and b to show the difference from other conditions(M, A or B).
    If my guess was right, the FROM clause would be something like the following.
    Code:
    FROM   TBLM
     RIGHT OUTER JOIN
           TBLA
      FULL OUTER JOIN
           TBLB
       ON  TBLA.KEY1= TBLB.KEY1
       AND TBLA.KEY2= TBLB.KEY2
       AND TBLA.KEY3= TBLB.KEY3
       ON  TBLM.KEY1 = COALESCE(TBLA.KEY1, TBLB.KEY1)
       AND TBLM.KEY2 = COALESCE(TBLA.KEY2, TBLB.KEY2)
    Last edited by tonkuma; 03-23-09 at 16:32.

  15. #15
    Join Date
    Mar 2009
    Posts
    10
    Thank You, TONKUMA.. I learned a lot from this recent SQL Query Help...lot of useful inputs from you and that too writing queries in a CONTEMPORARY way ..different from traditional DB2 way.

    Many thanks again.

Posting Permissions

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