Query Results cut short with Multiple tables & GROUP_CONCAT

[Profitweb]

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:

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

[Old Pedant]

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.]

[Old Pedant]

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.

[Profitweb]

I have been playing with that query for a while. I keep getting the same error.. even after I stripped it down:

SELECT U.email
FROM (
     SELECT email FROM jos_users
) AS U

MySQL said: Documentation
#1046 - No database selected

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

[Old Pedant]

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:

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:

    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.