Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: What kind of Join should i use ?

Re: What kind of Join should i use ?

From: Nossova Elena <nossova_at_quantum.de>
Date: Wed, 29 Dec 1999 12:08:07 +0100
Message-ID: <3869EB97.1B8F3A6@quantum.de>


Try this:

select ms3.program, ms3.res, ms3.ls , nvl(ms1_val ,0) + nvl(ms2_val, 0) from ms1, ms2,
(

        select a.program program, b.res res, c.ls ls
        from
        (select distinct program from
                (select program from ms1 union all select program from ms2)) a,
        (select distinct res from ms1) b,
        (select distinct ls from ms2) c
) ms3
where           ms3.p = ms1.p(+)
        and     ms3.res = ms1.res(+)
        and     ms3.p = ms2.p(+)
        and     ms3.ls = ms2.ls(+)

/
Elena Nossova

Parvinder Singh wrote:

> Hi all
>
> I am really confused with my following problem ...
>
> These are my two tables
>
> SQL> select * from ms1;
>
> PROGRAM RES MS1_VAL
> ------------ ------------ ---------
> Program - A R1 10
>
> SQL> select * from ms2;
>
> PROGRAM LS MS2_VAL
> ------------ ------------ ---------
> Program - B LS02 11
> Program - A LS01 12
> Program - A LS02 20
>
> now what i want is the following resultant set
>
> Program Res LS MS1_VAL + MS2_VAL
> --------------------------------------------------------------------------------
>
> Program - A R1 LS01 21 (10 + 12)
> Program - A R1 LS02 30 (10 + 20)
> Program - B R1 LS01 0/NULL (coz Program - B and R1 doesn't
> exist in ms1 and Program - B and LS01 does not exist in ms2)
> Program - B R1 LS02 11 (coz Program - B and R1
> doesn't exist in ms1 whereas Program - B and LS02 does exist in ms2
> and its value is 11)
>
> What kind of join should i use so as to achieve the above result ? I
> have tried a lot of things but nothing works . I am not listing down my
> stupid queries which i tried
>
> I hope i am clear with the problem !
>
> Regards & Thanks in advance
>
> ~Parvinder
Received on Wed Dec 29 1999 - 05:08:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US