CODEWITHSUNDEEP
sqloracle-database
Roy Devjyoti
Roy Devjyoti

Reputation: 127

How can I achieve a Join for merging two tables FULL OUTER JOIN and avoid duplicates?

I am trying to join TABLE A and TABLE B to a new table. I want to avoid duplicates but I want to retain all the records of the table.

I have already tried Full Outer Join,Left outer Join but I can't achieve the desired result.

TABLE A
======================
CLIENT_ID   SUPPLIER_NO  INVOICE_COUNT VOLUME
ABC         1234         10            456
GEF             4567         20        657      
ERT         8912         30        567
THE         6872         42        781

TABLE B
========================
CLIENT_ID   SUPPLIER_NO REJECTED_COUNT  REJECTED_VOLUME
ERT     8912        56          102
HJI     7823        34          781
REW     6721        45          632

FINAL TABLE(WHAT IT SHOULD LOOK LIKE)
========================
CLIENT_ID SUPPLIER_NO INVOICE_COUNT VOLUME REJECTED_COUNT REJECTED_VOLUME
ABC   1234        10         456   NULL or 0      NULL or 0 
GEF   4567        20         657   NULL or 0      NULL or 0     
ERT   8912        30         567   56         102
THE   6872        42         781   NULL or 0      NULL or 0     
HJI   7823        NULL       NULL  34         781
REW   6721        NULL       NULL  45         632

My query is

select   A.client_id
      ,  A.supplier_no
      ,  count(*)        as Rejected_Count 
      , sum(TOTAL_AMT)   as Rejected_Volume
      , count(*)         as InvoiceCount 
      , sum(INVOICE_AMT) as Volume  
from TABLEA A 
     FULL OUTER JOIN TABLEB B 
         ON A.client_id=B.client_id 
        AND A.SUPPLIER_NO=B.SUPPLIER_NO
group by  A.client_id,  A.supplier_no
order by A.client_id,  A.supplier_no;

I am not able to achieve this output whether I do a join with client_id and supplier no. In cases where the client_id is not present in TABLE A after traversing I want the contents from TABLE B avoiding duplicates. The Table A is just the results of a query and table B is same, and I want to merge those results.

Upvotes: 0

Views: 196

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think a using clause is the simplest solution:

select client_id, supplier_no,
       count(*)        as Rejected_Count,
       sum(TOTAL_AMT)   as Rejected_Volume,
       count(*)         as InvoiceCount ,
       sum(INVOICE_AMT) as Volume  
from TABLEA A full join
     TABLEB B 
     using (client_id, SUPPLIER_NO)
group by client_id, supplier_no
order by client_id, supplier_no;

An alternative is generous helpings of coalesce():

select coalesce(a.client_id, b.client_id) as client_id,
       coalesce(a.supplier_no, b.supplier_no) as supplier_no,
       count(*)        as Rejected_Count,
       sum(TOTAL_AMT)   as Rejected_Volume,
       count(*)         as InvoiceCount ,
       sum(INVOICE_AMT) as Volume  
from TABLEA A full join
     TABLEB B 
     on a.client_id = b.client_id and
        a.SUPPLIER_NO = b.SUPPLIER_NO
group by coalesce(a.client_id, b.client_id),
         coalesce(a.supplier_no, b.supplier_no)
order by coalesce(a.client_id, b.client_id),
         coalesce(a.supplier_no, b.supplier_no);

Upvotes: 1

Popeye
Popeye

Reputation: 35900

I think this can be achieved using UNION ALL and GROUP BY as following:

SELECT CLIENT_ID, SUPPLIER_NO,
MAX(INVOICE_COUNT) AS INVOICE_COUNT,
MAX(VOLUME) AS VOLUME,
MAX(REJECTED_COUNT) AS REJECTED_COUNT,
MAX(REJECTED_VOLUME) AS REJECTED_VOLUME
FROM
(SELECT CLIENT_ID, SUPPLIER_NO, INVOICE_COUNT, VOLUME, NULL AS REJECTED_COUNT, NULL REJECTED_VOLUME
FROM TABLEA
UNION ALL
SELECT CLIENT_ID, SUPPLIER_NO, NULL AS INVOICE_COUNT, NULL AS VOLUME, REJECTED_COUNT, REJECTED_VOLUME
FROM TABLEB)
GROUP BY CLIENT_ID, SUPPLIER_NO;

Use aggregate function according to your requirement. I have used max

Cheers!!

Upvotes: 1

umair qayyum
umair qayyum

Reputation: 286

Fix your join to:

FULL OUTER JOIN 
    TABLEB B ON A.client_id = B.client_id 
             AND A.SUPPLIER_NO = B.SUPPLIER_NO

As of now it is joining on the same column from same table

Upvotes: 0

Related Questions