Re: distinct in union

From: Rob Burton <burton.rob_at_gmail.com>
Date: Wed, 6 Oct 2010 08:56:09 -0700 (PDT)
Message-ID: <b34b5dff-2267-420e-ac1b-68a0d246e9ea_at_a36g2000yqc.googlegroups.com>



On Oct 6, 10:32 am, Michał Jabłoński <michal.jablo..._at_gmail.com> wrote:
> On 6 Paź, 11:10, Carlos <miotromailcar..._at_netscape.net> wrote:
>
>
>
> > On Oct 6, 10:45 am, Micha³ Jab³oñski <michal.jablo..._at_gmail.com>
> > wrote:
>
> > > The following query:
> > > select a_id,b, c from table1
> > > union all
> > > select a2_id,b2,c2 from table2
>
> > > gives me a result:
>
> > > 1,2,3
> > > 2,2,3
>
> > > but I want only values from second table. What is the most efficient
> > > way to do that?
>
> > Query the second table ONLY?
>
> > WTF!
>
> > Cheers.
>
> > Carlos.
>
> Sorry, I deleted part:
> but I want only values from second table if b=b2 and c=c2

This might be what you want. I think it handles other rows how you want them to be.

create table t1 (a number,b number,c number); Table created.
create table t2 (a number,b number,c number); Table created.
insert into t1 values (1,2,3);
1 row created.
insert into t2 values (2,2,3);
1 row created.
commit;
Commit complete.
select nvl(t2.a,t1.a),nvl(t2.b,t1.b),nvl(t2.c,t1.c) from t1 full outer join t2 on t1.b = t2.b and t1.c = t2.c;
NVL(T2.A,T1.A) NVL(T2.B,T1.B) NVL(T2.C,T1.C) -------------- -------------- --------------

             2 2 3 insert into t1 values (3,8,9);
1 row created.
insert into t2 values (4,11,12);
1 row created.
insert into t1 values (5,11,12);
1 row created.
insert into t1 values (5,11,12);
select nvl(t2.a,t1.a),nvl(t2.b,t1.b),nvl(t2.c,t1.c) from t1 full outer join t2 on t1.b = t2.b and t1.c = t2.c;
NVL(T2.A,T1.A) NVL(T2.B,T1.B) NVL(T2.C,T1.C) -------------- -------------- --------------

             2              2              3
             4             11             12
             3              8              9
Received on Wed Oct 06 2010 - 10:56:09 CDT

Original text of this message