Search code examples
mysqlsqlhaving

How to find duplicates in one field which are not duplicates in another

I'm trying to extract account numbers and postcodes from a table, where the postcodes are duplicates but the account numbers are not, (only where a postcode exists)

For example a table called mailing with the following fields:-

acc....custname.............postcode
B102...BILLYS SHOES.........LE54 2YZ
B104...BILLYS SHOES.........LE54 2YZ
D200...FANSY................RM5 6LW
D200...FANSY(2ND ACCOUNT)...RM5 6LW
T502...WELLIES R US.........
T502...WELLIES R US.........
    ...THE SHOE SHOP........DN54 05Y
X102...THE SHOE SHOP........DN54 05Y

So, i've tried to group by postcode having a count of > 1 which works fine, and displays

acc....custname.............postcode
B102...BILLYS SHOES.........LE54 2YZ
B104...BILLYS SHOES.........LE54 2YZ
D200...FANSY................RM5 6LW
D200...FANSY(2ND ACCOUNT)...RM5 6LW
    ...THE SHOE SHOP........DN54 05Y
X102...THE SHOE SHOP........DN54 05Y

but I need to be able to only display where the account number is also not duplicated, ie as below

acc....custname.............postcode
B102...BILLYS SHOES.........LE54 2YZ
B104...BILLYS SHOES.........LE54 2YZ
    ...THE SHOE SHOP........DN54 05Y
X102...THE SHOE SHOP........DN54 05Y

Code:

SELECT *
FROM mailing
WHERE postcode IS NOT NULL AND postcode <> '' AND
postcode IN (SELECT postcode FROM mailing group by postcode HAVING COUNT(postcode) > 1)
ORDER BY postcode;

Have tried to use another counter on the account number but don't get any data back at all

Solution

  • You can use NOT EXISTS and EXISTS for the 2 conditions:

    SELECT m.* FROM mailing m
WHERE
  NOT EXISTS (
    SELECT 1 FROM mailing
    WHERE acc = m.acc AND custname <> m.custname
  )
  AND
  EXISTS (
    SELECT 1 FROM mailing
    WHERE postcode = m.postcode and acc <> m.acc
  )

    If you just want records where postcodes are duplicated, but only if they're for different account numbers then use only EXISTS:

    SELECT m.* FROM mailing m
WHERE
  EXISTS (
    SELECT 1 FROM mailing
    WHERE postcode = m.postcode and acc <> m.acc
  )