SELECT MIN(X.MEMBERNAME),
TRIM(X.EMAIL) AS EMAIL
FROM (SELECT DISTINCT MEMBERNAME,
EMAIL
FROM (SELECT MEMBER.MEMBERNAME,
MEMBERADDR.EMAIL1 AS EMAIL
FROM MEMBER,
MEMBERADDR
WHERE (MEMBER.MEMBER_ID = MEMBERADDR.MEMBER_ID)
AND (MEMBER.FLAG = 0
AND MEMBERADDR.EMAIL1 IS NOT NULL)
UNION ALL
SELECT MEMBER.MEMBERNAME,
MEMBERADDR.EMAIL2 AS EMAIL
FROM MEMBER,
MEMBERADDR
WHERE (MEMBER.MEMBER_ID = MEMBERADDR.MEMBER_ID)
AND (MEMBER.FLAG = 0
AND MEMBERADDR.EMAIL2 IS NOT NULL)) MEMBER) X
GROUP BY TRIM(X.EMAIL)
SELECT MIN(X.MEMBERNAME),
TRIM(X.EMAIL) AS EMAIL
FROM (SELECT MEMBER.MEMBERNAME,
MEMBERADDR.EMAIL1 AS EMAIL
FROM MEMBER,
MEMBERADDR
WHERE (MEMBER.MEMBER_ID = MEMBERADDR.MEMBER_ID)
AND (MEMBER.FLAG = 0
AND MEMBERADDR.EMAIL1 IS NOT NULL)
UNION
SELECT MEMBER.MEMBERNAME,
MEMBERADDR.EMAIL2 AS EMAIL
FROM MEMBER,
MEMBERADDR
WHERE (MEMBER.MEMBER_ID = MEMBERADDR.MEMBER_ID)
AND (MEMBER.FLAG = 0
AND MEMBERADDR.EMAIL2 IS NOT NULL)) X
GROUP BY TRIM(X.EMAIL)
SELECT MIN(X.MEMBERNAME),
X.EMAIL AS EMAIL
FROM (SELECT MEMBER.MEMBERNAME,
TRIM(LOWER(MEMBERADDR.EMAIL1)) AS EMAIL
FROM MEMBER,
MEMBERADDR
WHERE (MEMBER.MEMBER_ID = MEMBERADDR.MEMBER_ID)
AND (MEMBER.FLAG = 0
AND MEMBERADDR.EMAIL1 IS NOT NULL)
UNION
SELECT MEMBER.MEMBERNAME,
TRIM(LOWER(MEMBERADDR.EMAIL2)) AS EMAIL
FROM MEMBER,
MEMBERADDR
WHERE (MEMBER.MEMBER_ID = MEMBERADDR.MEMBER_ID)
AND (MEMBER.FLAG = 0
AND MEMBERADDR.EMAIL2 IS NOT NULL)) X
GROUP BY X.EMAIL
Select distinct X.MEMBERNAME,X.EMAIL
From (
/* Formatted on 07/03/2011 11:41:34 AM (QP5 v5.136.908.31019) */
SELECT DISTINCT MEMBERNAME,EMAIL FROM (SELECT MEMBER.MEMBERNAME, MEMBERADDR.EMAIL1 AS EMAIL
FROM MEMBER, MEMBERADDR
WHERE (MEMBER.MEMBER_ID = MEMBERADDR.MEMBER_ID) AND (MEMBER.FLAG = 0 AND MEMBERADDR.EMAIL1 IS NOT NULL)
UNION ALL
/* Formatted on 07/03/2011 11:41:34 AM (QP5 v5.136.908.31019) */
SELECT MEMBER.MEMBERNAME, MEMBERADDR.EMAIL2 AS EMAIL
FROM MEMBER, MEMBERADDR
WHERE (MEMBER.MEMBER_ID = MEMBERADDR.MEMBER_ID) AND (MEMBER.FLAG = 0 AND MEMBERADDR.EMAIL2 IS NOT NULL)) MEMBER ) X
ORDER BY X.MEMBERNAME