Distinct email addresses in 2 tables with different field names

[Maziar Aflatoun]

Hi everyone,

I have 2 tables with table A containing an 'email' field and table B
containing 2 fields 'primaryemail' and 'secondaryemail'. Now is it possible
to issue a query that would return only the unqiue email addresses in these
3 fields? So a long list with no duplicate emails(distinct).

Thank you
Maz.

[Lukas Bradley]

> I have 2 tables with table A containing an 'email' field and table B

containing 2 fields 'primaryemail' and 'secondaryemail'. Now is it possible
to issue a query that would return only the unqiue email addresses in these
3 fields? So a long list with no duplicate emails(distinct).

Depends on the version of MySQL you are using. If you are using 4.1
review the manual section on subqueries, particularly this section:

http://www.mysql.com/documentation/m...STS_subqueries

Example: What kind of store is present in all cities?

SELECT DISTINCT store_type FROM Stores S1
WHERE NOT EXISTS (
SELECT * FROM Cities WHERE NOT EXISTS (
SELECT * FROM Cities_Stores
WHERE Cities_Stores.city = Cities.city
AND Cities_Stores.store_type = Stores.store_type));

If you're not using 4.1, then you won't be able to do it in a single
query. Give more information about the version you are using, and I'll
try to help.

Lukas

[Lukas Bradley]

> I have 2 tables with table A containing an 'email' field and table B

containing 2 fields 'primaryemail' and 'secondaryemail'. Now is it possible
to issue a query that would return only the unqiue email addresses in these
3 fields? So a long list with no duplicate emails(distinct).

Depends on the version of MySQL you are using. If you are using 4.1
review the manual section on subqueries, particularly this section:

http://www.mysql.com/documentation/m...STS_subqueries

Example: What kind of store is present in all cities?

SELECT DISTINCT store_type FROM Stores S1
WHERE NOT EXISTS (
SELECT * FROM Cities WHERE NOT EXISTS (
SELECT * FROM Cities_Stores
WHERE Cities_Stores.city = Cities.city
AND Cities_Stores.store_type = Stores.store_type));

If you're not using 4.1, then you won't be able to do it in a single
query. Give more information about the version you are using, and I'll
try to help.

Lukas