CODEWITHSUNDEEP
mysqlsqlduplicates
oldeagle
oldeagle

Reputation: 9

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

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!

Upvotes: 0

Views: 48

Answers (3)

sticky bit
sticky bit

Reputation: 37472

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);

Upvotes: 1

Job Curtis
Job Curtis

Reputation: 175

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;

Upvotes: 0

GMB
GMB

Reputation: 222432

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

Upvotes: 0

Related Questions