#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2001
    Location
    UK
    Posts
    9
    Rep Power
    0

    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)
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 1999
    Location
    Seattle
    Posts
    133
    Rep Power
    16
    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
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2001
    Location
    UK
    Posts
    9
    Rep Power
    0
    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.

IMN logo majestic logo threadwatch logo seochat tools logo