CODEWITHSUNDEEP
sqloracle-databaseselectjoin
canimbenim
canimbenim

Reputation: 649

Oracle get data just from table A, just from table B and shared

I need to take data for reports. I have 2 tables A and B. Table A has many inserts for the same id and can have just entries in this table or share data with table B, table B can have just entries in this table or share data with table A.

i.e.

 Create table A (
  id number,
  name varchar2(30),
  seq number
  );

Create table B (
  name_s varchar2 (30),
  a_id number
  );

insert into A (id, name,seq) values (1, 'first',1);
insert into A (id, name,seq) values (3, 'first',2);
insert into A (id, name,seq) values (1, 'second',3);
insert into A (id, name,seq) values (2, 'first',4);
insert into A (id, name,seq) values (2, 'second',5);
insert into A (id, name,seq) values (1, 'third',6);
insert into A (id, name,seq) values (3, 'second',7);
insert into A (id, name,seq) values (1, 'fourth',8);
insert into A (id, name,seq) values (1, 'fifth',9);
insert into A (id, name,seq) values (1, 'sixth',10);
insert into A (id, name,seq) values (2, 'third',11);
insert into A (id, name,seq) values (3, 'third',12);

insert into B (name_s, a_id) values ('sale1', 3);
insert into B (name_s, a_id) values ('sale2', null);
insert into B (name_s, a_id) values ('sale3', 1);

Now I would like to return data: everything from table A but not in B, everything from table B but not in A and everything what they share, but if a B is connected with A - it should return the most recent entry from table A with the a_id from B table.

So I would expect to be returned:

column headers: A_id, A_name, A_seq, B_name
    --everything what is not in table B
      (2, 'first',4, null);
      (2, 'second',5, null);
      (2, 'third',11, null);
    --everything what is not in table A
     (null, null,null, 'sale2');
    --everything what is shared
     (3, 'third', 12,'sale1');
     (1, 'sixth', 10,'sale3');

My solution is to run 3 queries to get these data:

--just from table A
select * from A where id not in (select nvl(a_id,-1) from B);
--just from table B
select * from B where a_id is null;
--shared
select * from B,A where B.a_id = A.id and A.seq = 
(select max(seq) from A where A.id = B.a_id);

Is there better way to do it using join (I tried but it always return me more than I expect)? To run just one query or two rather than 3?

Here is link to fiddle example: http://sqlfiddle.com/#!4/9fdb3/3

Thanks

Upvotes: 0

Views: 151

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

If I understand the logic correctly, you can do what you want with a full outer join and some additional logic:

select coalesce(a.id, b.a_id) as id,
       a.name,
       a.seq,
       b.name_s,
       (case when a.id is not null and b.name_s is not null
             then 'Both'
             when a.id is not null
             then 'A-Only'
             else 'B-Only'
        end) as which
from (select a.*,
             row_number() over (partition by id order by seq desc) as seqnum
      from a
     ) a full outer join
     b
     on a.id = b.a_id
where b.name_s is not null and coalesce(a.seqnum, 1) = 1 or b.name_s is null;

The twist is handling the strange sequencing logic in the where clause -- you want only the most recent A when there is a match, and all of them when there is not one. This produces your desired results in SQL Fiddle.

Upvotes: 3

Related Questions