Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    4

    Question Unanswered: Compare Two Duplicate records

    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 ????

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please do a SHOW CREATE TABLE for both tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please do a SHOW CREATE TABLE for both tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2009
    Posts
    64
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •