0

I have a table with below structure:

create table TEST_REFUND_CASHOUT
(
  f_gdate              DATE,
  trackingcode         VARCHAR2(4000),
  cashout_trackingcode VARCHAR2(4000),
  cashout_date         DATE
)

Given the sample data:

 f_gdate    trackingcode   cashout_trackingcode   cashout_date 
--------------------------------------------------------------
4/29/2025      6088            195                5/15/2025
4/29/2025      6088            201                5/15/2025
4/29/2025      6088            186                5/18/2025
4/29/2025      6088            129                5/19/2025
5/18/2025      1754            186                5/18/2025
5/18/2025      1754            129                5/19/2025
5/18/2025      1754            814                5/24/2025
5/18/2025      1754            191                5/24/2025
5/27/2025      1213            333                5/28/2025

From above table, I need to see only those trackingcode for which all the cashout_trackingcodes do not belong to another trackingcode. So, I should only see trackingcode = 1213 because its cashout_trackingcode = 333 belongs only to this trackingcode. Other two remaining trackingcode have intersections. For instance, cashout_trackingcode = 129 belongs to another trackingcode as well which is 1754.

The solution I have found is below:

select *
  from TEST_REFUND_CASHOUT t 
 where not exists (select 1
          from TEST_REFUND_CASHOUT a
         where t.trackingcode <> a.trackingcode
           and a.cashout_trackingcode = t.cashout_trackingcode)

however it does not work and gives me wrong result ! thanks in advance

Improve this question
1
  • Is anybody here? Commented Nov 16 at 6:49

2 Answers 2

Reset to default
1

Return the trackingcode only if all its cashout_trackingcodes belong exclusively to that trackingcode

For each cashout_trackingcode check how many different trackingcode exists, if there are more than one they will be filtered out from the HAVING SUM (...) = 0

SELECT trackingcode
FROM TEST_REFUND_CASHOUT t
GROUP BY trackingcode
HAVING SUM(
        CASE 
          WHEN (SELECT COUNT(DISTINCT trackingcode)
                FROM TEST_REFUND_CASHOUT t2
                WHERE t2.cashout_trackingcode = t.cashout_trackingcode
               ) > 1
          THEN 1 ELSE 0
        END
      ) = 0;

See example (The demo is using MySQL because it gave run failed if I used Oracle)

Note, the issue with your query giving wrong results might be because it checks row by row not by set/group

Improve this answer
1

I think your query description should be "Get all trackingcode where none of it's cashout_trackingcode belongs to another". If so below query should work(Tested it in PostgreSQL, hopefully work in Oracle too)

SELECT t1.trackingcode
FROM test_refund_cashout t1
WHERE NOT EXISTS (
    SELECT t2.cashout_trackingcode
    FROM test_refund_cashout t2
    WHERE t2.trackingcode = t1.trackingcode
      AND EXISTS (
          SELECT 1
          FROM test_refund_cashout t3
          WHERE t3.cashout_trackingcode = t2.cashout_trackingcode
            AND t3.trackingcode <> t2.trackingcode
      )
) group by t1.trackingcode;
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.