Oracle – Data that contains multiple results from IN

I have a dataset of different transaction ids of type 1,2,3,4,5,6,7 as well as many other columns

What I’m trying to do is create different scenarios such as

• Contains transactions 1 only
• Contains 1,5 and 7

I’ve started off with a CTE called ALL_CONTRACTS that contains transaction ids of type 1,2,3,4,5,6,7

WITH CONTRACTS AS (
            
         SELECT
                
                CONT.AGREEMENT_ID,
                CONT.ACCOUNT_NUMBER,
                TRAN.TRAN_ID
                
                FROM CONTRACTS CONT
                
                JOIN TRANSACTIONS TRAN
                ON CONT.AGREEMENT_ID = TRAN.AGREEMENT_ID
                
                WHERE TRAN.TRAN_ID IN (1,2,3,4,5,6,7)
                
                )
                
         SELECT 
         
         CT1.AGREEMENT_ID
        ,CT1.ACCOUNT_NUMBER   
        
         FROM CONTRACTS CT1
         
         WHERE CT1.TRAN_ID IN ('1') AND CT1.TRAN_ID NOT IN (2,3,4,5,6,7)

What’s happening is other contracts that contain a 2,3,4,5,6,7 transaction are showing as they also contain a 1

So how do I pull out only contracts that have a 1 only and not the others?

Answer

Assuming you are trying to find the TRAN_ID for each AGREEMENT_ID/ACCOUNT_NUMBER pair then you can use:

SELECT AGREEMENT_ID,
       ACCOUNT_NUMBER,
       TRAN_ID
FROM   (
  SELECT c.AGREEMENT_ID,
         c.ACCOUNT_NUMBER,
         t.TRAN_ID,
         COUNT(
           CASE WHEN t.TRAN_ID IN (2,3,4,5,6,7) THEN 1 END
         ) OVER (PARTITION BY c.AGREEMENT_ID, c.ACCOUNT_NUMBER)
           AS num_other_tarnsactions
  FROM   CONTRACTS c
         INNER JOIN TRANSACTIONS t
         ON (c.AGREEMENT_ID = t.AGREEMENT_ID)
  WHERE  t.TRAN_ID IN (1,2,3,4,5,6,7)
)
WHERE  tran_id = 1
AND    num_other_tarnsactions = 0;

Which, for the sample data:

CREATE TABLE contracts (agreement_id, account_number) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL UNION ALL
SELECT 3, 3 FROM DUAL UNION ALL
SELECT 4, 4 FROM DUAL UNION ALL
SELECT 5, 5 FROM DUAL;

CREATE TABLE transactions (agreement_id, tran_id) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 2, 3 FROM DUAL UNION ALL
SELECT 3, 2 FROM DUAL UNION ALL
SELECT 3, 3 FROM DUAL UNION ALL
SELECT 3, 4 FROM DUAL UNION ALL
SELECT 3, 5 FROM DUAL UNION ALL
SELECT 3, 6 FROM DUAL UNION ALL
SELECT 4, 1 FROM DUAL UNION ALL
SELECT 4, 2 FROM DUAL UNION ALL
SELECT 4, 3 FROM DUAL UNION ALL
SELECT 4, 4 FROM DUAL UNION ALL
SELECT 4, 5 FROM DUAL UNION ALL
SELECT 4, 6 FROM DUAL UNION ALL
SELECT 4, 7 FROM DUAL UNION ALL
SELECT 5, 1 FROM DUAL UNION ALL
SELECT 5, 1 FROM DUAL;

Outputs:

AGREEMENT_ID	ACCOUNT_NUMBER	TRAN_ID
1	1	1
5	5	1
5	5	1

db<>fiddle here