A full outer join query [message #683783] |
Sun, 21 February 2021 15:35 |
Amine
Messages: 376 Registered: March 2010
|
Senior Member |
|
|
Hi all,
I have this schema :
drop table a;
create table a
(
id number ,
dpt_id number
);
insert into a values (0, 100);
insert into a values (1, 100);
insert into a values (2, 100);
insert into a values (3, 100);
insert into a values (5, 200);
insert into a values (6, 200);
insert into a values (7, 200);
drop table b;
create table b
(
id number ,
dpt_id number
);
insert into b values (1, 100);
insert into b values (2, 100);
insert into b values (3, 100);
insert into b values (4, 100);
insert into b values (5, 200);
insert into b values (6, 200);
insert into b values (8, 200);
I run this query :
select
, a.id
, a.dpt_id
-- ---
, b.id
, b.dpt_id
from a full outer join b
on (a.id = b.id
and a.dpt_id = b.dpt_id)
and get this result :
ID DPT_ID ID DPT_ID
--------- --------- --------- ---------
1 100 1 100
2 100 2 100
3 100 3 100
4 100
5 200 5 200
6 200 6 200
8 200
7 200
0 100
Now I am looking for a query to get this
GRP ID DPT_ID ID DPT_ID
--------- --------- --------- --------- ---------
100 1 100 1 100
100 2 100 2 100
100 3 100 3 100
100 4 100
100 0 100
200 5 200 5 200
200 6 200 6 200
200 8 200
200 7 200
I want to add the GRP column so I can get all the rows grouped by dpt_id.
Of course, this is a reduced example. Actually we have hundreds of dpt_id.
Thanks in advance Gurus !
|
|
|
Re: A full outer join query [message #683784 is a reply to message #683783] |
Sun, 21 February 2021 15:58 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
select nvl(a.dpt_id,b.dpt_id) grp,
a.id,
a.dpt_id,
b.id,
b.dpt_id
from a
full outer join
b
on a.id = b.id
and a.dpt_id = b.dpt_id
order by grp,
b.id,
a.id
/
GRP ID DPT_ID ID DPT_ID
---------- ---------- ---------- ---------- ----------
100 1 100 1 100
100 2 100 2 100
100 3 100 3 100
100 4 100
100 0 100
200 5 200 5 200
200 6 200 6 200
200 8 200
200 7 200
9 rows selected.
SQL>
SY.
[Updated on: Sun, 21 February 2021 16:00] Report message to a moderator
|
|
|
|