Pull data from two DB2 tables, UNION ALL

Question

I have two tables.. In output result I want to merge data from both. So assume I have to use UNION statement. What I need in result set:

rows 1 and 2 from TABLE1 only.
all rows from TABLE2 where TABLE2/COL4 = TABLE1/COL1 in these were selected in #1, (these are 1 and 3 in TABLE2)
In output results substitute data in COL2 for TABLE2 rows with data from COL2 of TABLE1

TABLE1------------------------------------------
COL1  COL2    COL3
1     aaa1    eee1
2     bbb1    fff1
3     ccc1    ggg1
4     ddd1    hhh1

TABLE2------------------------------------------
COL1  COL2    COL3    COL4
1     aaa2    eee2    1
2     bbb2    fff2    4
3     ccc2    ggg2    1
4     ddd2    hhh2    3

RESULT I NEED-----------------------------------
COL1  COL2    COL3
1     aaa1    eee1
2     bbb1    fff1
1     aaa1    eee2
3     aaa1    ggg2

I need something like this:

SELECT#1 COL1, COL2, COL3 FROM TABLE1 WHERE COL1 IN ('1','2')
UNION ALL
SELECT#2 COL1, COL2 <AS SELECT#1.COL2>, COL3 FROM TABLE2 WHERE <COL4 IN SELECT#1.COL1>

OK, but what about #3? (In output results substitute data ......). And I need data from both tables in one column COL2 — VladP

jarlh jarlh · Accepted Answer · 2015-03-03T19:42:00

select col1, col2, col3 from table1
WHERE COL1 IN (1,2)
union
select t2.col1,
       coalesce((select col2 from table1
                 WHERE COL1 IN (1,2)
                   AND COL1 = t2.col4),
                t2.col2),
       t2.col3
from table2 t2 join table1 t1 on t1.col1 = t2.col1 and col4 in
  (select col1 from table1 WHERE COL1 IN (1,2))

Gives me:

SQL>select * from table1
SQL&WHERE COL1 IN (1,2)
SQL&union
SQL&select t2.col1,
SQL&       coalesce((select col2 from table1
SQL&                 WHERE COL1 IN (1,2)
SQL&                   AND COL1 = t2.col4),
SQL&                t2.col2),
SQL&       t2.col3
SQL&from table2 t2 join table1 t1 on t1.col1 = t2.col1 and col4 in
SQL&  (select col1 from table1 WHERE COL1 IN (1,2))
SQL&;

       col1 col2   col3
       ==== ====   ====
          1 aaa1   eee1
          2 bbb1   fff1
          1 aaa1   eee2
          3 aaa1   ggg2

4 rows found

Pull data from two DB2 tables, UNION ALL

1 Answers