WEB Another SQL QUERY question...

Discussion in 'OT Technology' started by brds, Jul 15, 2008.

  1. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,642
    Likes Received:
    13
    Location:
    Atlanta
    I'm trying to display a list of all users in the database. However, do to other columns in the field, some users are in the database twice. for example, i might have something like this:

    userID | Name | email address | jobid
    1______ user1___ [email protected]____ 1
    2______ user2___ [email protected]____ 2
    3______ user1___ [email protected]____ 4

    (You'll have to excuse the _ marks... had to use them to make spacing look right)


    If you notice, userid 1 and 3 relate to the same person. I'm trying to select only unique users, not unique jobid's.

    Is there a way to say something along the lines of:

    SELECT * FROM TABLE_NAME WHERE UNIQUE(email);

    :dunno:

    Any help would be appreciated
     
  2. intrktevo

    intrktevo New Member

    Joined:
    Oct 18, 2004
    Messages:
    5,781
    Likes Received:
    0
    Location:
    UCF
    SELECT DISTINCT email, * FROM TABLE_NAME;
     
  3. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,642
    Likes Received:
    13
    Location:
    Atlanta
    I ran my query as:
    Code:
    SELECT DISTINCT Email, adminID, Name, userName FROM TICKETS_ADMIN
    and i'm still getting two users who's emails are the same address in the DB.
     
  4. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,642
    Likes Received:
    13
    Location:
    Atlanta
    anybody?
     
  5. Ender0910

    Ender0910 woot!

    Joined:
    Jun 1, 2004
    Messages:
    3,039
    Likes Received:
    0
    Location:
    Redmond/Bay Area
    If you get two rows with the same email with this query, something besides the emails must be different between the two (adminID, Name, or userName).

    Distinct, like a GROUP BY clause, will basically combine all identical rows into one. If they're not identical, you get multiple rows.

    So what is different between the two rows with the same email?
     
  6. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,642
    Likes Received:
    13
    Location:
    Atlanta
    there's a field (that i dont need for this specific query) that Speciality that contains the differences between rows with similar emails....
     
  7. Ender0910

    Ender0910 woot!

    Joined:
    Jun 1, 2004
    Messages:
    3,039
    Likes Received:
    0
    Location:
    Redmond/Bay Area
    Well, if this non-identical column isn't selected in your result set, that shouldn't affect the DISTINCT ness. Can you paste what the two result rows with the same email look like?
     
  8. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,642
    Likes Received:
    13
    Location:
    Atlanta
    Right now (for testing purposes) i have put only three rows in the table. They look like this:

    adminID___-___Name_-_____Email_____-___Speciality_-_userName_-_Password
    [email protected]-______1_____-_michaUN_-_*Password*
    [email protected]-______2_____-_michaUN_-_*Password*
    ___3___-_ [email protected]-______3_____-_AlexUN_-_*Password*

    (again, you'll have to excuse the underscores, but i used them to help with spacing)

    I'm trying to list each person only once using distinct
     
  9. EkriirkE

    EkriirkE Zika Xenu OT Supporter

    Joined:
    Jan 11, 2004
    Messages:
    14,799
    Likes Received:
    0
    Location:
    Dublin & San Francisco, CA
    You could do a subquery...
    Code:
    SELECT * FROM  TICKETS_ADMIN WHERE id IN (SELECT MIN(id) FROM TICKETS_ADMIN GROUP BY Email)
    
     
  10. Ender0910

    Ender0910 woot!

    Joined:
    Jun 1, 2004
    Messages:
    3,039
    Likes Received:
    0
    Location:
    Redmond/Bay Area
    Your above query had repeats because it selected adminID and those are different for every row. Remove that and you'll get the correctly Distinct listing of users.
     

Share This Page