CODEWITHSUNDEEP
mysql
Pacman
Pacman

Reputation: 55

Query MySQL for data that appears more than once, but only if another field differs

I apologize in advance, I don't even really know how to properly word this question, so I'll just describe the scenario:

I have a database with rows that contain several pieces of data, lets say for example there is "PHONE", "ADDRESS" and "NAME" columns.

I'd like to be able to run a query that looks for instances of "NAME" appearing more than once (duplicates), but only if the "PHONE" AND "ADDRESS" field doesn't match.

If the NAME entry is a duplicate of another row, and PHONE OR(!) ADDRESS also matches, I'm not interested.

Thanks for your time!

Upvotes: 1

Views: 165

Answers (2)

Tin Tran
Tin Tran

Reputation: 6202

you probably want to do a self JOIN and then check on those conditions, depending on what you want the OR below might be an AND too, try it and see.

SELECT T1.NAME,T1.PHONE,T1.ADDRESS,T2.NAME as NAME2,T2.PHONE as PHONE2,T2.ADDRESS as ADDRESS2
FROM yourTable T1 INNER JOIN yourTable T2 ON T1.NAME = T2.NAME AND (T1.PHONE > T2.PHONE OR T1.ADDRESS > T2.ADDRESS)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You are looking for names that have different phones or address:

select name
from table t
group by name
having min(phone) <> max(phone) or
       min(address) <> max(address);

If you want to require that both are different, then use and instead of or.

Upvotes: 1

Related Questions