NOT distinct???

[anders2002]

Hi,

Is it possible to run a simple query that will return entries in a table that are NOT distinct? Like:

Select !distinct from categoryassocs

The table contacts ID1 and ID2 and I need to know where there are duplicates?

Any ideas?

Thanks,

Anderz

 

[hos2]

you can do

select max(id1), count(id1) as nrofids from contacts where nrofids > 1 group by id1

here you should get all the rows which have the same value more then once

or do you have 2 seperate tables which you want to compare ???
or are id1 and id2 fieldnames in contacts where you don't want to have the same values ??

 

[anders2002]

Thanks for that, but what I actually want to find is this:

ContactID CategoryID
1 1
2 2
2 2
3 3
4 4
5 5

What I would like to find is a sql statement that would identify that 2 2 has been written twice to the database.

Can this be done?

 

[swampBoogie]

select contactid,categoryid, count(*) as nrOfOccurences
 from categoryassocs
 group by contactid,categoryid
  having count(*) > 1

 

[anders2002]

You are a mysql god!

Thanks ever so much!

Anderz