Home » SQL & PL/SQL » SQL & PL/SQL » A full outer join query (11.2.0.3)
A full outer join query [message #683783] Sun, 21 February 2021 15:35 Go to next message
Amine
Messages: 371
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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

Re: A full outer join query [message #683808 is a reply to message #683784] Mon, 22 February 2021 14:16 Go to previous message
Amine
Messages: 371
Registered: March 2010
Senior Member

Sometimes our brain stops to do his elementary job Smile
Thanks SY
Previous Topic: Duplicate Records in output file
Next Topic: Generate month wise data using SQL (Sort data based on month)
Goto Forum:
  


Current Time: Fri Mar 29 04:27:10 CDT 2024