Complete results from HAVING

[JoSno]

Hello all,

I have a table with four simple columns. One column (UID) of this table contains a "unique" field, but there can be multiple entires of this "unique" field. The reason being that if an update record has been entered this field will be in two places and I am in the process of finding these multiple entries.

I've entered the following :

SELECT uid FROM myTable GROUP BY (UID) HAVING count(uid) > 1;

and this returns all the uid's that occur more than once.

I am now having to write all of those records to a temp table then search for all details of records with uid's in the temp table. It would be nice if I could say SELECT * then I would have all of the records there in one step, but unfortunately I have had to do a "group by" so I can only see one set of the duplicated record when I would really like both. Any ideas on how to display all records (both records in the pairs aswell) that have duplicate entries in a table?

Cheers
Jo

 

[r937]

prior to mysql 4.1, you cannot do this in one query

save your duplicate UIDs in a separate table, and join


rudy

 

[JoSno]

i thought so, never mind.

just out of interest when you say you can do it in mysql 4.1, do you mean using nested selects or is there a much fancier way of doing things?

Jo

 

[swampBoogie]

A subselect

select * from mytable
where uid in (
SELECT uid FROM myTable 
GROUP BY (UID) 
HAVING count(uid) > 1)

or a derived table

select * from mytable join (
SELECT uid FROM myTable 
GROUP BY (UID) 
HAVING count(uid) > 1) as dt
using(uid)

are the possible ways

 

[JoSno]

cheers, i think that's only from 4.1 though.

Jo