SQL Query Help Please--

TopNotch · #1 (**permalink**) 03-21-09, 07:21

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.

tonkuma · #2 (**permalink**) 03-21-09, 09:04

Quote:

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.

tonkuma · #3 (**permalink**) 03-21-09, 09:26

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.

TopNotch · #4 (**permalink**) 03-21-09, 09:27

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

tonkuma · #5 (**permalink**) 03-21-09, 09:46

Quote:

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.

TopNotch · #6 (**permalink**) 03-22-09, 22:25

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 ..

tonkuma · #7 (**permalink**) 03-22-09, 23:19

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.

tonkuma · #8 (**permalink**) 03-22-09, 23:24

For this condition
o x x -100

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

tonkuma · #9 (**permalink**) 03-22-09, 23:32

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.

TopNotch · #10 (**permalink**) 03-23-09, 05:49

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 ..

tonkuma · #11 (**permalink**) 03-23-09, 08:30

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.

TopNotch · #12 (**permalink**) 03-23-09, 11:22

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?

TopNotch · #13 (**permalink**) 03-23-09, 13: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..

tonkuma · #14 (**permalink**) 03-23-09, 14:33

Your join clause

Quote:

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

Quote:

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.

Quote:

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)

TopNotch · #15 (**permalink**) 03-23-09, 15:51

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech