Hello All,

It's Very urgent for me !!!!

Problem Description :

I have two table 1) user 2) userInfo
I want to Find Duplicate user, For that I write one Query is
SELECT ub.userId, ub.username, COUNT(ub.username) AS userInfo_NumOccurrences
FROM userinfo AS ub
GROUP BY ub.username, ub.AffiId
HAVING (COUNT(ub.username) > 1) ;

Its Showing Two duplicate username but Also i want compare affId of duplicate users For Example :

user table | userInfo table
user.username = userInfo.username
user.affId = userInfo.affId

If Both are Same then n then it will be consider as duplicate records

So how can i write query for that ? ? ? ?? ? ?? Or any modificationon above query ????

please do a SHOW CREATE TABLE for both tables

please do a SHOW CREATE TABLE for both tables

I'm assuming duplicate user means username with affid that exists in both tables. If that's the case,

SELECT username, affid, count(*) FROM (
SELECT username, affid FROM user
UNION ALL
SELECT username, affid FROM userInfo
) AS all users
GROUP BY 1, 2
HAVING COUNT(*) > 1