Re: distinct in union

From: Michał Jabłoński <michal.jablonsk_at_gmail.com>
Date: Sat, 9 Oct 2010 10:31:31 -0700 (PDT)
Message-ID: <c8fa5dc3-b63f-4bfc-8d9b-341b66473c1b_at_g18g2000yqk.googlegroups.com>

On 6 Paź, 17:56, Rob Burton <burton...._at_gmail.com> wrote:
> 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

It is exactly what I want. Thanks a lot Received on Sat Oct 09 2010 - 12:31:31 CDT