How to find duplicates in 2 columns not 1

Question

In my database, on a particular table there are several cloumns but the only 2 of interest right now are stone_id and upcharge_title. Individually they can each have duplicates, but they should never have a duplicate of BOTH of them having the same value.

For example stone_id can have duplicates as long as for each duplicate upsharge title is different, and vice verca. But say for example stone_id = 412 and upcharge_title = "sapphire" that combinitation should only occur once. I hope I am making this clear.

This is ok:

stone_id = 412 upcharge_title = "sapphire"
stone_id = 412 upcharge_title = "ruby"

This is ok:

stone_id = 412 upcharge_title = "sapphire"
stone_id = 413 upcharge_title = "sapphire"

This is NOT ok:

stone_id = 412 upcharge_title = "sapphire"
stone_id = 412 upcharge_title = "sapphire"

Can anyone tell me a query that will find duplicates in both fields? And if possible is there a way to set my data-base to not allow that?

I am using MySQL version 4.1.22

THANKS!!!

UPDATE: Thanks so much so far guys, Can anyone please tell me a query that could DELETE the duplicates but leave 1 copy. And please tell me how to set up the composite key. I do know how to setup primary and unique keys but not composite keys.

Thanks!!!

Answer 1

You should set up a composite key between the two fields. This will require a unique stone_id and upcharge_title for each row.

As far as finding the existing duplicates try this:

select   stone_id,
         upcharge_title,
         count(*)
from     your_table
group by stone_id,
         upcharge_title
having   count(*) > 1

Answer 2

To find the duplicates:

select stone_id, upcharge_title from tablename group by stone_id, upcharge_title having count(*)>1

To constrain to avoid this in future, create a composite unique key on these two fields.

Answer 3

You can find duplicates like this..

Select
    stone_id, upcharge_title, count(*)
from 
    particulartable
group by 
    stone_id, upcharge_title
having 
    count(*) > 1

Answer 4

Incidentally, a composite unique constraint on the table would prevent this from occurring in the first place.

ALTER TABLE table
    ADD UNIQUE(stone_id, charge_title)

(This is valid T-SQL. Not sure about MySQL.)

Answer 5

this SO post helped me, but i too wanted to know how to delete and keep one of the rows... here's a PHP solution to delete the duplicate rows and keep one (in my case there were only 2 columns and it is in a function for clearing duplicate category associations)

$dupes = $db->query('select *, count(*) as NUM_DUPES from PRODUCT_CATEGORY_PRODUCT group by fkPRODUCT_CATEGORY_ID, fkPRODUCT_ID having count(*) > 1');
if (!is_array($dupes))
    return true;
foreach ($dupes as $dupe) {
    $db->query('delete from PRODUCT_CATEGORY_PRODUCT where fkPRODUCT_ID = ' . dupe['fkPRODUCT_ID'] . ' and fkPRODUCT_CATEGORY_ID = ' . $dupe['fkPRODUCT_CATEGORY_ID'] . ' limit ' . ($dupe['NUM_DUPES'] - 1);
}

the (limit NUM_DUPES - 1) is what preserves the single row...

thanks all