Creating a SQL command for unique items in one column, but similar items in another?

Question

My SQL knowledge is a bit more limited than I'd like to admit. What I'm attempting to do is query a single table. In that table I want to return and group items with the same "name" (column), but only if their "address" (column) DOESN'T match. I have been trying to find existing SO questions, because I'm sure someone has faced this same issue, but my searches haven't yielded good results.

ID   Name        Address
---  ---------   ------------
1    Tom         123 Fake St.
2    Paul        81 Second Ave.
3    Mark        1001 Market St.
4    Tom         123 Fake St.
5    Tom         903 Castle St.
6    Pete        14 Circle Dr.

The expectation would be that I could return the results for "Tom" for both of his addresses, because he has more than 1, and because they don't match.

ID   Name        Address
---  ---------   ------------
1    Tom         123 Fake St.
4    Tom         123 Fake St.
5    Tom         903 Castle St.

What exactly do you mean by "'does not match'"? – PM 77-1 Mar 06 '23 at 18:24 — PM 77-1, Mar 06 '23 at 18:24
Hi @RyanInBinary, which version of MySQL are you using? – Junjie Mar 06 '23 at 18:28 — Junjie, Mar 06 '23 at 18:28

score 1 · Accepted Answer · answered Mar 06 '23 at 18:24

This will give you a list of names with different address

select name 
from (
  select name, count(distinct address) as c
  from table_you_did_not_name
  group by name
) x
where x.c > 1

This will give you the results you asked for

select *
from table_you_did_not_name
where name in (
   select name 
   from (
     select name, count(distinct address) as c
     from table_you_did_not_name
     group by name
   ) x
   where x.c > 1
)

score 0 · Answer 2 · answered Mar 06 '23 at 18:35

You can use correlated subqueries to caclulate the umber of entires

SELECT
DISTINCT `Name`
FROM address a1
WHERE (SELECT COUNT(*) FROM address a2 WHERE a1.name = a2.name) > 1
  AND (SELECT COUNT(DISTINCT `Address`) FROM address a2 WHERE a1.name = a2.name) > 1

Name
Tom

Or a MySql 8 Version of that

WITH CTE as
(SELECT name,
  COUNT(*) count_,
  COUNT(DISTINCT `Address`) count_a
FROM address
GROUP By name)
SELECT
DISTINCT `Name`
FROM CTE WHERE count_ > 1 AND count_a > 1

Name
Tom

fiddle

Creating a SQL command for unique items in one column, but similar items in another?

2 Answers2