Return Duplicate emails along with User Ids that are different-CodePudding

I'm running into an issue with a duplicate query and I hope you guys can help.
Essentially what I want to do is find and list of the duplicate emails associated with different userids

My query is:

select UserId, acitveid, email, userstatusid
from (select  u.UserId, u.acitveid, cd.email, u.userstatusid,
            count(*)over (partition by cd.email) as cnt
            from ContactDetails cd 
            join  UserContactDetails ucd on ucd.ContactDetailsId = cd.ContactDetailsId
            join  dbo.[User] u on u.UserId = ucd.UserId ) ua
    where cnt >1

The issue I have with the above query is that it is returning the same userids for some of the results so it looks like:

Userid   AcitveId      email          UserStatusid
123      1             [email protected]     1
123      1             [email protected]     1
135      1             [email protected]     1
142      1             [email protected]     1

The results Im looking for are simply:

Userid   AcitveId      email          UserStatusid
135      1             [email protected]     1
142      1             [email protected]     1

CodePudding user response：

WITH base AS (
SELECT DISTINCT u.UserId
        ,u.acitveid
        ,cd.email
        ,u.userstatusid
        ,
    FROM ContactDetails cd
    JOIN UserContactDetails ucd ON ucd.ContactDetailsId = cd.ContactDetailsId
    JOIN dbo.[User] u ON u.UserId = ucd.UserId
)
,duplicate_emails AS (
    SELECT email
        ,count(userId) AS cnt
    FROM base
    GROUP BY 1
    HAVING count(userId) > 1
)
SELECT b.*
FROM base b
JOIN duplicate_emails de ON b.email = de.email

CodePudding user response：

A self join across Email = email and id <> id would work fine here. That said, your request and lack of sample data means that we are largely guessing based off the query and sample output you have provided. The below should get you pretty close and, if you update your OP, I am sure we can get you exactly what you're after.

SELECT ActiveUser.UserID Active_UserID,
    ActiveUser.ActiveID Active_ActiveID,
    ContactDetails.email AS Email,
    DuplicateUser.UserID AS Dup_UserID,
    DuplicateUser.ActiveID As Dup_ActiveID
FROM ContactDetails INNER JOIN 
    ContactDetails AS Duplicate ON ContactDetails.email = Duplicate.email AND ContactDetails.UserID <> Duplicate.UserID INNER JOIN
    UserContactDetails AS ActiveUserContactDetails ON ActiveUserContactDetails.ContactDetailsID = ContactDetails.ContactDetailsID INNER JOIN
    dbo.[User] AS ActiveUser ON ActiveUser.UserID = ActiveUserContactDetails.UserID INNER JOIN
    UserContactDetails AS DuplicateUserContactDetails ON DuplicateUserContactDetails.ContactDetailsID = Duplicate.ContactDetailsID INNER JOIN
    dbo.[User] AS DuplicateUser ON DuplicateUser.UserID = UserContactDetails.UserID