What is the easiest to combine 2 seperate recordsets with related records?
The following example has two different recordsets F1 and F2 but are joined with F0.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> with t0 as
2 (
3 select 1 as id, 'Example 1' as f0 from dual union all
4 select 2 as id, 'Example 2' as f0 from dual
5 ),
6 t1 as
7 (
8 select 1 as id, 'a' as f1 from dual union all
9 select 1 as id, 'b' as f1 from dual union all
10 select 2 as id, 'aa' as f1 from dual union all
11 select 2 as id, 'bb' as f1 from dual union all
12 select 2 as id, 'cc' as f1 from dual union all
13 select 2 as id, 'dd' as f1 from dual
14 ),
15 t2 as
16 (
17 select 1 as id, 'x' as f2 from dual union all
18 select 1 as id, 'y' as f2 from dual union all
19 select 1 as id, 'z' as f2 from dual union all
20 select 2 as id, 'ww' as f2 from dual
21 )
22 select f0,f1,f2
23 from t0, t1, t2
24 where t0.id = t1.id
25 and t0.id = t2.id;
F0 F1 F2
--------- -- --
Example 1 a x
Example 1 b x
Example 1 a y
Example 1 b y
Example 1 a z
Example 1 b z
Example 2 aa ww
Example 2 bb ww
Example 2 cc ww
Example 2 dd ww
10 rows selected.
desired output:
F0 F1 F2
--------- -- --
Example 1 a x
Example 1 b y
Example 1 z
Example 2 aa ww
Example 2 bb
Example 2 cc
Example 2 dd