Question 20550 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.
Tags:

2

Answers


  1. 0

    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
)
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search