'How to fetch only CH_PAY_MODE loan_id from this table?

loan_id coll_date coll_pay_mode
8007571771 APR-2022 CH
8007571771 FEB-2022 CH
8007571771 JAN-2022 CH
8007571771 MAR-2022 CH
8007571771 MAR-2022 CL
8007571771 MAY-2022 CH
8007636540 APR-2022 CH
8007636540 JAN-2022 CH
8007636540 MAR-2022 CH
8007636540 MAY-2022 CH

For the data above, there is two loan_id given in the table, per month collection using CH or CL, so i wanted to fetch loan_id which is not paying in CL in any month.

sqloraclegroup-by


Solution 1:[1]

One of the method to achieve your desired result is to use EXISTS clause -

SELECT DISTINCT loan_id
  FROM YOUR_TABLE T1
 WHERE coll_pay_mode = 'CH'
  AND NOT EXISTS (SELECT NULL
                    FROM YOUR_TABLE T2
                   WHERE T1.loan_id = t2.loan_id
                     AND T2.coll_pay_mode = 'CL')

Solution 2:[2]

One option in to use conditional aggregation along with HAVING clause such as

SELECT loan_id
  FROM t
 GROUP BY loan_id
HAVING SUM(CASE WHEN coll_pay_mode = 'CL' THEN 1 ELSE 0 END) = 0

Solution 3:[3]

You can achieve this with LEFT JOIN as well:

SELECT distinct loan_id
FROM yourtable t1
LEFT JOIN yourtable t2
ON loan_id = t2.loan_id and
   t2.col1_pay_mode = 'CL'
WHERE t1.col1_pay_mode = 'CH' AND
      t2.col1_pay_mode IS NULL;

So, we do a self left-join to make sure that there is no t2 match to t1 with col1_pay_mode being CL and we select distinct values.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ankit Bajpai
Solution 2 Barbaros Özhan
Solution 3 Lajos Arpad

Related Questions