Here is my testing table data:
ID Name Payment_Date Fee Amt
1 BankA 2016-04-01 100 20000
2 BankB 2016-04-02 200 10000
3 BankA 2016-04-03 100 20000
4 BankB 2016-04-04 300 20000
ID Name Payment_Date Fee Amt SameDataExistYN
1 BankA 2016-04-01 100 20000 Y
2 BankB 2016-04-02 200 10000 N
3 BankA 2016-04-03 100 20000 Y
4 BankB 2016-04-04 300 20000 N
select t.*, iif((select count(*) from testing where name=t.name and fee=t.fee and amt=t.amt)=1,'N','Y') as SameDataExistYN from testing t
select t.*, case when ((b.Name = t.Name)
and (b.Fee = t.Fee) and (b.Amt = t.Amt)) then 'Y' else 'N' end as SameDataExistYN
from testing t
left join ( select Name, Fee, Amt
Group By Name, Fee, Amt
Having count(*)>1 ) as b on b.Name = t.Name
and b.Fee = t.Fee
and b.Amt = t.Amt
There are several approaches, with differences in performance characteristics.
One option is to run a correlated subquery. This approach is best suited if you have a suitable index, and you are pulling a relatively small number of rows.
SELECT t.id , t.name , t.payment_date , t.fee , t.amt , ( SELECT 'Y' FROM testing s WHERE s.name = t.name AND s.fee = t.fee AND s.amt = t.amt AND s.id <> t.id LIMIT 1 ) AS SameDataExist FROM testing t WHERE ... LIMIT ...
The correlated subquery in the SELECT list will return a Y when there is at least one "matching" row found. If no "matching" row is found, SameDataExist column will have a value of NULL. To convert the NULL to an 'N', you could wrap the subquery in an IFULL() function.
Your method 2 is a workable approach. The expression in the SELECT list doesn't need to do all those comparisons, those have already been done in the join predicates. All you need to know is whether a matching row was found... just testing one of the columns for NULL/NOT NULL is sufficient.
SELECT t.id , t.name , t.payment_date , t.fee , t.amt , IF(s.name IS NOT NULL,'Y','N') AS SameDataExists FROM testing t LEFT JOIN ( -- tuples that occur in more than one row SELECT r.name, r.fee, r.amt FROM testing r GROUP BY r.name, r.fee, r.amt HAVING COUNT(1) > 1 ) s ON s.name = t.name AND s.fee = t.fee AND s.amt = t.amt WHERE ...
You could also make use of an EXISTS (correlated subquery)