Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Nov 2009
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Query Results cut short with Multiple tables & GROUP_CONCAT

    I am querying 2 tables, using UNION, & group_concat to create an email address list to copy & paste in our emails. I'm having 2 problems:
    1. Query results are being cut short.
    2. Unable to remove duplicates (WHERE RocketEmail <> OtherEmail, etc., is causing more duplicates or errors for me).


    Generated by: phpMyAdmin 2.8.0.1 / MySQL 5.0.51a-log
    SQL query:
    Code:
    Select GROUP_CONCAT( DISTINCT email SEPARATOR '; ' ) AS email
    FROM jos_users
    UNION
    Select GROUP_CONCAT( DISTINCT RocketEmail SEPARATOR '; ' ) AS email 
    FROM jos_asm_members
    UNION
    Select GROUP_CONCAT( DISTINCT OtherEmail SEPARATOR '; ' ) AS email FROM jos_asm_members
    ORDER BY email
    LIMIT 0, 1000 ;
    RESULTS:
    ; jos**agle@gmail.com; rc**2@embarqmail.com; mm**@live.com; ev**@yahoo.com; da**ll@yahoo.com; ge**l@gmail.com; j**s@gmail.com; c**n@gmail.com

    al**t@gmail.com; b**ra@rockets.utoledo.edu; co*er@rockets.utoledo.edu; d*f@rockets.utoledo.edu; d*1@hotmail.com; ga**n@gmail.com; g*k@rockets.utoledo.edu; J**un@gmail.com; j**e@gmail.com; K*k@gmail.com; k*3@gmail.com; K*k@rockets.utoledo.edu; Kimberly. <-- RESULTS CUT OFF

    do*r@rockets.utoledo.edu; st*ler@rockets.utoledo.edu; j*le@rockets.utoledo.edu; za*r@rockets.utoledo.edu; m*e@rockets.utoledo.edu; c*r@rockets.utoledo.edu; al*rk@rockets.utoledo.edu; br*t@rockets.utoledo.edu; k*d@rockets.utoledo.edu; ma<-- RESULTS CUT OFF


    Thank you for your suggestions on how I can improve this query.

    Kim

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,941
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    http://dev.mysql.com/doc/refman/5.1/...concat_max_len

    Why not just use
    SELECT DISTINCT ...
    in your 3 queries? Why get 3 long seimicolon delimited strings in the first place?

    If you want to directly use them for sending out email, you will likely have problems with that many email addresses in a single SEND, anyway.

    But even if that's what you want, it's no big deal to simply run through all the records concatenating the values to a single string with the semicolon delimiter.

    [If you are using ASP, by any chance, there's even a built-in function to do that.]
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,941
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    Code:
    SELECT DISTINCT U.email 
    FROM (
        SELECT DISTINCT email FROM jos_users
        UNION
        Select DISTINCT RocketEmail AS email FROM jos_asm_members
        UNION
        Select DISTINCT OtherEmail AS email FROM jos_asm_members
    ) AS U
    Not sure whether putting the extra DISTINCT's in there will help or hurt. Could try it both ways.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New to the CF scene
    Join Date
    Nov 2009
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have been playing with that query for a while. I keep getting the same error.. even after I stripped it down:
    Code:
    SELECT U.email
    FROM (
         SELECT email FROM jos_users
    ) AS U
    
    MySQL said: Documentation
    #1046 - No database selected
    Code:
    SELECT DISTINCT U.email
    FROM (
         SELECT DISTINCT email FROM jos_users
         UNION
         SELECT DISTINCT RocketEmail AS email FROM jos_asm_members
    ) AS U
    LIMIT 0 , 30
    
    MySQL said: Documentation
    #1046 - No database selected 
    I'm on a hosted server, using the SQL window to run queries. I'm not sure if that could block me from making sub-queries or not. I'm pretty new at all of this.

    Unfortunately, I do not have ASP. Any other ideas?

    Kim
    Last edited by Profitweb; 11-29-2009 at 10:54 PM.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,941
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    The ASP part was sort of a joke.

    Anyway, the apparent problem is that you are on a pretty old version of MySQL that doesn't support subqueries.

    If this query works:
    Code:
    SELECT DISTINCT email FROM jos_users
    UNION
    SELECT DISTINCT RocketEmail AS email FROM jos_asm_members
    then that has to be the problem.

    In the long run, you'd be better off finding a different host with support for MySQL 5.1 or better.

    In the short run, you can probably solve this with a combination of MySQL and PHP.

    Use the query:
    Code:
        SELECT DISTINCT email FROM jos_users
        UNION
        Select DISTINCT RocketEmail AS email FROM jos_asm_members
        UNION
        Select DISTINCT OtherEmail AS email FROM jos_asm_members
        ORDER BY email;
    And then use PHP to eliminate the duplicates.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Profitweb (12-05-2009)


  •  

    Tags for this Thread

    Posting Permissions

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