How do u select records where two or more fields are the same?

Hi,

Need some help with a little SQL code.

I have a table with fields:

id
username
password

I want to select all records where there are two or more equal passwords. Is it possible?

(in case it may help Im using MySQL)

You can always join a table to itself for comparison purposes. This code assumes that your table is called mytable and that id is unique.

SELECT DISTINCT t1.id, t1.name, t1.password
FROM mytable t1, mytable t2
WHERE t1.password = t2.password and t1.id != t2.id

Don

That didnt seem to work for me.

I figured our part of it.

SELECT password FROM table
GROUP BY password HAVING count( * ) > 1

But that returns all passwords which are used more than once, which ispartly what I want. Im having to then make a query for each of those passwords returned by the above to get the rest of the table information.

Its slow. There is another method, using "In" (Im guessing a query in a query), but MySQL doesnt seem to want to support it.