Mahdi Yousef Mahdi Yousef - 5 months ago 9
SQL Question

SQl query Join and set duplicate column value in to null

I want when join 2 table in sql query then in result
duplicate column cell One of the duplicate entries become to null.
My first table is:

id corp_code pay_authority_no authority_price status
1 C286 210995 85020000 True
2 C286 210879 61040000 True
3 C139 212475 77708280 True
4 C139 212465 77878320 True
5 C296 216177 101335000 True
13 C321 214526 56680000 True


and second table is:

id pay_authority_no order_kind order_no
2 210879 Reorder 84182
1 210995 Reorder 83251
4 212465 Sup 459950
3 212475 Sup 459948
15 212475 Sup 65878
13 214526 Reorder 86019
14 214526 Reorder 86020
5 216177 Reorder 83715


and also result is:

corp_code pay_authority_no authority_price order_no order_kind
C139 212465 77878320 459950 Sup
C139 212475 77708280 459948 Sup
C139 212475 77708280 65878 Sup
C286 210879 61040000 84182 Reorder
C286 210995 85020000 83251 Reorder
C296 216177 101335000 83715 Reorder
C321 214526 56680000 86019 Reorder
C321 214526 56680000 86020 Reorder


i want result looks like this:

corp_code pay_authority_no authority_price order_no order_kind
C139 212465 77878320 459950 Sup
C139 212475 77708280 459948 Sup
C139 212475 Null or 0 65878 Sup
C286 210879 61040000 84182 Reorder
C286 210995 85020000 83251 Reorder
C296 216177 101335000 83715 Reorder
C321 214526 56680000 86019 Reorder
C321 214526 Null or 0 86020 Reorder


please resolve this!!

Answer

Try this:

SELECT t1.corp_code, t1.pay_authority_no, 
       CASE 
          WHEN t2.rn = 1 THEN t1.authority_price
          ELSE NULL
      END authority_price,
      t2.order_no, t2.order_kind
FROM tab1 AS t1
JOIN (
   SELECT order_kind, order_no, pay_authority_no,
          ROW_NUMBER() OVER (PARTITION BY pay_authority_no ORDER BY id) AS rn
   FROM tab2
) AS t2 ON t1.pay_authority_no = t2.pay_authority_no
ORDER BY t1.corp_code,t1.pay_authority_no
Comments