Forum Stats

  • 3,734,032 Users
  • 2,246,861 Discussions
  • 7,857,003 Comments

Discussions

combining 2 seperate recordsets with related records

474007
474007 Member Posts: 317
edited November 2010 in SQL & PL/SQL
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

Comments

  • NicloeiW
    NicloeiW Member Posts: 1,811
    what output you are getting is correct one, is there any other column which is missing here...
  • 474007
    474007 Member Posts: 317
    There is no column missing and this is the output that I want:
    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
  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    What's the logic behind the result set that you want, based on the example given?
  • NicloeiW
    NicloeiW Member Posts: 1,811
    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

    whats logic for these results...

  • 474007
    474007 Member Posts: 317
    The logic is that I want to combine two sql statements into one for easier maintenance of an info-maker's report.


    I want to combine the resultsets of the following sql-statements:
    select fo, f1
    from t0, t1
    where t0.id = t1.id
    and
    select fo, f2
    from t0, t2
    where t0.id = t2.id
    Is the wanted output difficult to make with plain sql?
  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    Can you in words describe what you want? "Combining two SQL statements into one" would call for a UNION ALL, like:
    select f0, null f1, f2
    from t0, t2
    where t0.id = t2.id
    union all
    select f0, f1, null
    from t0, t1
    where t0.id = t1.id
    But this gives a different result set than the one you're after. Be as specific as you can.
  • 450441
    450441 Member Posts: 2,525
    Explain why

    Example 1 b x
    Example 1 a y

    should not be included. Why not? They meet the conditions you have described. Thus there must be another condition that excludes them. What is it? Not to mention why the ww is being suppressed in

    Example 2 bb
    Example 2 cc
    Example 2 dd
  • 474007
    474007 Member Posts: 317
    For each group F0 the distinct F1 and F2 values may only be visible once.
  • 474007
    474007 Member Posts: 317
    Thanks for the help so far.

    I am a little bit closer but don't understand why rn of f2 is 4 instead of 3. If I understand that I could solve it myself.
    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, rn,
    23 max(case when f1 is null then null else f1 end) f1,
    24 max(case when f2 is null then null else f2 end) f2
    25 from
    26 (
    27 (
    28 select f0, f1, null f2, rownum rn
    29 from t0, t1
    30 where t0.id = t1.id
    31 )
    32 union all
    33 (
    34 select f0, null f1, f2,rownum rn
    35 from t0, t2
    36 where t0.id = t2.id
    37 )
    38 )
    39 group by f0, rn;

    F0 RN F1 F2
    --------- ---------- -- --
    Example 1 1 a x
    Example 1 2 b y
    Example 1 3 z
    Example 2 3 aa
    Example 2 4 bb ww
    Example 2 5 cc
    Example 2 6 dd

    7 rows selected.
  • 474007
    474007 Member Posts: 317
    I got it! One more question though, can this be done in a easier way? Like using xmlsequence, analytical functions or other tricks that I don't know about.
    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
    24 (
    25 select f0, rn,
    26 max(case when f1 is null then null else f1 end) f1,
    27 max(case when f2 is null then null else f2 end) f2
    28 from
    29 (
    30 (
    31 select f0, f1, null f2, row_number() over (partition by f0 order by null) rn
    32 from t0, t1
    33 where t0.id = t1.id
    34 )
    35 union all
    36 (
    37 select f0, null f1, f2, row_number() over (partition by f0 order by null) rn
    38 from t0, t2
    39 where t0.id = t2.id
    40 )
    41 )
    42 group by f0, rn
    43 );

    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

    7 rows selected.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited November 2010
    It is very good that you prepared "select statement" of "with clause".

    We may use "full join" which is used by derived rank.
    Then, we may use "inner join".
    col f1 for a10
    col f2 for a10
    
    with t0 as (
    select 1 as id, 'Example 1' as f0 from dual union all
    select 2 as id, 'Example 2' as f0 from dual ),
    t1 as (select 1 as id, 'a' as f1 from dual union all
    select 1 as id, 'b' as f1 from dual union all
    select 2 as id, 'aa' as f1 from dual union all
    select 2 as id, 'bb' as f1 from dual union all
    select 2 as id, 'cc' as f1 from dual union all
    select 2 as id, 'dd' as f1 from dual ),
    t2 as (
    select 1 as id, 'x' as f2 from dual union all
    select 1 as id, 'y' as f2 from dual union all
    select 1 as id, 'z' as f2 from dual union all
    select 2 as id, 'ww' as f2 from dual)
    select c.f0,a.F1,b.f2
         from (select id,f1,Row_Number() over(partition by id order by f1) as Rank from t1) a
    full join (select id,f2,Row_Number() over(partition by id order by f2) as Rank from t2) b
           on (a.id = b.id and a.Rank = b.Rank)
         join t0 c on (c.id= nvl(a.id,b.id))
    order by 1,2,3;
    
    F0        F1   F2
    --------- ---- -----
    Example 1 a    x
    Example 1 b    y
    Example 1 null z
    Example 2 aa   ww
    Example 2 bb   null
    Example 2 cc   null
    Example 2 dd   null
    similar solution of my site (written by Japanese language)
    http://www.geocities.jp/oraclesqlpuzzle/4-1.html
This discussion has been closed.