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.

CodePudding user response：

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   

CodePudding user response：

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')

CodePudding user response：

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.