SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Also available in Large Si's Avatar
    Join Date
    Sep 2002
    Location
    Walsall, UK
    Posts
    1,911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trouble with SQL Distinct

    I'm trying to get a list of unique email addresses from a table (which unfortunately contains some addresses multiple times). At the moment, I'm using the following SQL:

    SELECT DISTINCT(EmailAddress), DownloadCustomer.CustomerID, DateCreated
    FROM DownloadCustomer
    LEFT JOIN Questionnaire01
    ON Questionnaire01.CustomerID = DownloadCustomer.CustomerID
    WHERE EntryNo IS NULL


    This still returns multiple entries of the same email address. I've just realised "GROUP BY EmailAddress" might work but can't work out where to put this in the command.

    Can anyone help?
    Si
    Are you a Photoshop Jedi Master? Prove it!

    Is funky house your bag? You'll love this!

    Voice
    , eyes, ears, body and hands.


  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    If you select other fields, the distinct will return multiple values. What distinct does is return distinct ROWS, not fields. If you have two customerIDs with the same email address, you'll get that email address twice. You'll need to handle the distinct email addresses in your code OR you'll need to break the query up into multipl queries and do them separately.

    Is there a reason you've got multiple customers with the same address? Why not just combine those customer records into one record? Would be much more efficient (and solve headaches) if you don't need duplicates....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    Also available in Large Si's Avatar
    Join Date
    Sep 2002
    Location
    Walsall, UK
    Posts
    1,911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers for that - makes sense now!

    For a quick fix, I just used ASP to eliminate duplicate records but I'll probably break it up into multiple records when I get some spare time.

    I would merge the accounts but it might cause some nasty errors with logging in and remote records associated with the customer id.
    Si
    Are you a Photoshop Jedi Master? Prove it!

    Is funky house your bag? You'll love this!

    Voice
    , eyes, ears, body and hands.


  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by bigsi
    I would merge the accounts but it might cause some nasty errors with logging in and remote records associated with the customer id.
    You'd obviously have to convert the remote records to have the "new" customer_id. It would be time consuming and you'd need to be careful, but it would probably help you in the long run.

    What you would probably want to do is email the person saying "Look, you've got two different account on our system. Which one do you use regularly so we can merge them into one." Lay the blame on some sort of accounting fuss or some such, and if you don't get a response, just pick one to keep and let them know which one is now their login.

    Just my $.02 (not worth much in todays economy)
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •