Dear all,

If I have table like this

A aa B bb
1 5
2 7
1 7
3 6


I want to make the result like this

A or B aa bb
1 5 7
2 7
3 6


A or B is a column has all data avaliable in both A and B


help please

NOVA schreef:
I'm sure there are better ways, but how about

select distinct nvl(t1.a,t2.b), t1.aa, t2.bb from example t1, example t2
where t1.a=t2.b
or ( t1.a is null and t2.b is not null and not exists (select 1 from
example t3 where t3.a=t2.b))
or ( t2.b is null and t1.a is not null and not exists (select 1 from
example t4 where t1.a=t4.b))

Shakespeare

On 10 ãÇÑÓ, 16:50, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
I know this way

I want better ways

thanks

"NOVA" <nova1427 (AT) gmail (DOT) com> a écrit dans le message de news: 63259416-7767-49eb-a10c-4da91fea9809...oglegroups.com...
On 10 ãÇÑÓ, 16:50, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
I know this way

I want better ways

thanks

--------------------------------

Post all the ways you know then we can of better ones.

Regards
Michel

NOVA schreef:
Well, you could at least have told us that..... ;-)
Shakespeare

On Mar 10, 8:14*am, NOVA <nova1... (AT) gmail (DOT) com> wrote:
Logically, it seems you have two tables in one. Not exactly a design I
would favor. but using that observation I suggest this:

drop table yourtable ;
create table yourtable
( A integer,
aa integer,
B integer,
bb integer ) ;

insert into yourtable values (1,5,null,null);
insert into yourtable values (2,7,null,null);
insert into yourtable values (null,null,1,7);
insert into yourtable values (null,null,3,6);

select A, aa from yourtable where A is not null;
select B, bb from yourtable where B is not null;

SELECT A, aa, bb
from
(select A, aa from yourtable where A is not null ) yourA ,
(select B, bb from yourtable where B is not null ) yourB
where yourA.A = yourB.B(+) ;

SELECT B, aa, bb
from
(select A, aa from yourtable where A is not null ) yourA ,
(select B, bb from yourtable where B is not null ) yourB
where yourA.A(+) = yourB.B;

SELECT A, aa, bb
from
(select A, aa from yourtable where A is not null ) yourA ,
(select B, bb from yourtable where B is not null ) yourB
where yourA.A = yourB.B(+)
UNION
SELECT B, aa, bb
from
(select A, aa from yourtable where A is not null ) yourA ,
(select B, bb from yourtable where B is not null ) yourB
where yourA.A(+) = yourB.B;

I'll let you decide if this is better (for whatever is your definition
of better).
(though I sure hope this isn't homework!!!)
Ed

On Mar 10, 9:14*am, NOVA <nova1... (AT) gmail (DOT) com> wrote:
select nvl(a, b), max(aa), max(bb) from t group by nvl(a, b)