CODEWITHSUNDEEP
sqlt-sql
Lynchie
Lynchie

Reputation: 1149

SQL - Group Value Column A, Only show in Column B has different values

I am trying to write a query to keep an eye out for when the same email address or telephone number is being used but the postcode is different.

I have a table like below:

Ref@   | PCode   | Email
----------------------------------
LYJX01 | B99 1AA | [email protected]
LYJX02 | B98 1AA | [email protected]
LYJX03 | B92 1DD | [email protected]
AHSF01 | B91 2BB | [email protected]

I want to pull out all the records for [email protected] because they have multiple different postcodes associated with their email address.

In an ideal world a postcode should be unique to the email address but if the postcode is ever different I wish to pull those policies.

I have so far written this:

SELECT pr.B@, pr.Ref@, pr.Pcode, pr.Email
FROM dbo.ic_Daprospect pr
WHERE pr.Email IN (
SELECT Email
FROM dbo.ic_Daprospect pr
GROUP BY pr.Email
HAVING COUNT(*) > 1
)

All this is doing though is pulling all the records where the they have multiple lines with the same postcode and the email address exists more than once.

How do I go about only showing where they have instances of the postcode changing?

Cheers.

Upvotes: 0

Views: 108

Answers (2)

iamdave
iamdave

Reputation: 12243

You want to initially find all emails that have more than one Postcode recorded against them, and then return all the records from the main table where the Email recorded is in this list:

select *
from dbo.ic_Daprospect pr
where exists(
            select null
            from dbo.ic_Daprospect pr2
            where pr.Email = pr2.Email    -- This matches any records with the same email as the one in your source table.
            group by pr2.Email            -- Group them to get one row per email.
            having count(distinct pr2.PCode) > 1    -- Then filter for where there is more than one PCode for that email.
            )

Using exists help with query performance as the query will stop processing the sub-select as soon as a match is found.

Upvotes: 1

Rahul Sawant
Rahul Sawant

Reputation: 1264

Try below along with requirement it should also exclude records where email id and Code combination is same.

  select *
    from dbo.ic_Daprospect pr
    where exists(
                select 1
                from dbo.ic_Daprospect pr2
                where pr.Email=pr2.Email and pr.Email||pr.PCode <> pr2.Email||pr2.PCode
                )

Hope it helps

Upvotes: 2

Related Questions