mySQL - Find find unique columns based on duplicates in another column

Question

I have been investigating this topic but it is not quite what I need and I do not understand how to solve it.

Table: companies

Columns: id, cust_number, cust_name

There may be many records with the same cust_number, but all such records should have identical cust_name.

The query I am trying to write should display all records where this is not the case - like this:

| id | cust_number | cust_name |
| -- | ----------- | --------- | 
| 10 |    800      | Acme LTD  | 
| 11 |    700      | Globex    | 
| 12 |    800      | Acme LTD  | 
| 13 |    444      | Globex    | 
| 14 |    800      | Acme LTT  |

From the table above the query should result in:

| id | cust_number | cust_name |
| -- | ----------- | --------- | 
| 10 |    800      | Acme LTD  | 
| 12 |    800      | Acme LTD  | 
| 14 |    800      | Acme LTT  |

Because there are more than 1 records with the same cust_number but all 3 records does not have identical cust_name.

Thanks for all help!

@AmanjotKaur, I have only tried the query in the post I linked to. — oldeagle, Mar 05 '20 at 12:47
@SalmanA, are you sure? The 3 cust_names are not all identical. — oldeagle, Mar 05 '20 at 12:47
@SalmanA it does, so long as you account for the fact that string comparison in MySQL will evaluate 'Acme LTD' and 'Acme Ltd' as equal, whereas this is _not_ what the author wants. — Job Curtis, Mar 05 '20 at 12:53
@SalmanA, I understand. I have modified the orginal post. Thanks. — oldeagle, Mar 05 '20 at 13:17

sticky bit · Answer 1 · 2020-03-05T12:59:47.097

1

You can use EXISTS to check for records with the same cust_number but different name.

SELECT c1.id,
       c1.cust_number,
       c1.cust_name
       FROM companies c1
       WHERE EXISTS (SELECT *
                            FROM companies c2
                                 WHERE c2.cust_number = c1.cust_number
                                       AND c2.cust_name <> c1.cust_name);

edited Mar 05 '20 at 12:59

answered Mar 05 '20 at 12:30

sticky bit

36,626
12
31
42

I have tried this but it does not return anything. Please note that it should show records with the same cust_number, not the same ID... – oldeagle Mar 05 '20 at 12:56
@oldeagle: Changed `id` to `cust_number`, see the edit. – sticky bit Mar 05 '20 at 13:00
Thanks, sticky bit. That worked but the query takes a very long time to run. I may have to think of another way to solve the problem. – oldeagle Mar 05 '20 at 15:22

score 0 · Answer 2 · answered Mar 05 '20 at 12:37

Assuming MySQL 8.0, you can use window functions for this:

select id, cust_number, cust_name
from (
    select 
        t.*,
        min(cust_name) over(partition by cust_number) min_cust_name,
        max(cust_name) over(partition by cust_number) max_cust_name
    from mytable t
) t
where min_cust_name <> max_cust_name

score 0 · Answer 3 · answered Mar 05 '20 at 12:41

Probably an issue you're running into here is that (usually) string comparison in MySQL is case-insensitive - see "How can I make SQL case sensitive string comparison on MySQL? ".

i.e. SELECT ('Acme LTD' = 'Acme Ltd'); returns 1 or TRUE.

You can circumvent this by comparing the binary representation of the strings; SELECT (BINARY 'Acme LTD' = BINARY 'Acme Ltd'); returns 0.

With that in mind, the following query should return what you're looking for;

SELECT DISTINCT t1.id
              , t1.cust_number
              , t1.cust_name
FROM my_table t1
         JOIN my_table t2 ON t2.cust_number = t1.cust_number AND
                             (BINARY t2.cust_name <> BINARY t1.cust_name)
ORDER BY t1.id;

mySQL - Find find unique columns based on duplicates in another column

3 Answers3