Oracle distinct

I have a query as follows

/* 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
 ORDER BY  MEMBERNAME

OUTPUT

AMARNATH KAMATH      amarnathkamath@gmail.com
BHARATHI KAMATH       amarnathkamath@gmail.com

This is correct as per DB and query.

But I dont want duplicate email ID in ouput no matter which record it takes. I mean i need distinct on email ID no matter which name it takes.

I mean i want output like
AMARNATH KAMATH      amarnathkamath@gmail.com

second one is not required since email is duplicate though name is different
GRChandrashekarAsked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
SELECT MIN(X.MEMBERNAME),
         X.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 UPPER(X.EMAIL)
0
 
Pratima PharandeCommented:
try this

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
0
 
pinkurayCommented:
use union in place of union all then tell us what is your output
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
pinkurayCommented:
also you can use what pratima_mcs: suggested
0
 
GRChandrashekarAuthor Commented:
@pratima_mcs:

No change in output
0
 
Pratima PharandeCommented:
Select min (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
gorup by X.EMAIL
 ORDER BY  X.MEMBERNAME
0
 
GRChandrashekarAuthor Commented:
@ pratima_mcs:

Error gorup by X.EMAIL
SQL command not properly ended
0
 
Pratima PharandeCommented:
Select min (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
group by X.EMAIL
 ORDER BY  X.MEMBERNAME
0
 
GRChandrashekarAuthor Commented:
Error
ORDER BY  X.MEMBERNAME
NOT A GROUP BY EXPRESSION
0
 
Pratima PharandeCommented:
try only this

Select min (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
group by X.EMAIL
0
 
GRChandrashekarAuthor Commented:
No error but output is still the same

AMARNATH KAMATH      amarnathkamath@gmail.com
BHARATHI KAMATH      amarnathkamath@gmail.com
0
 
SharathData EngineerCommented:
Do you have extra spaces before/after EMAIL? Try with TRIM function.
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)

Open in new window

0
 
Pratima PharandeCommented:
actully this need to work..is there any spcae in meail
try this


Select min (X.MEMBERNAME ),trim(both ' ' from X.EMAIL) as 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
group by trim(both ' ' from X.EMAIL)
0
 
SharathData EngineerCommented:
In fact I suggest UNION  instead of UNION ALL.
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)

Open in new window

0
 
GRChandrashekarAuthor Commented:
No luck so far in any of the queries

AMARNATH KAMATH      amarnathkamath@gmail.com
BHARATHI KAMATH      Amarnathkamath@Gmail.Com
0
 
Pratima PharandeCommented:
SELECT MIN(X.MEMBERNAME),
         UPPER(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 UPPER(X.EMAIL)
0
 
SharathData EngineerCommented:
try this
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

Open in new window

0
 
Pratima PharandeCommented:
SELECT MIN(X.MEMBERNAME),
         Lower(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 Lower(X.EMAIL)
0
 
slightwv (䄆 Netminder) Commented:
I realize this is already closed but why have the union/joins/??? at all?

SELECT MEMBER.MEMBERNAME, nvl(MEMBERADDR.EMAIL1,MEMBERADDR.EMAIL2) AS EMAIL
  FROM MEMBER, MEMBERADDR
 WHERE (MEMBER.MEMBER_ID = MEMBERADDR.MEMBER_ID) AND (MEMBER.FLAG = 0 AND (MEMBERADDR.EMAIL1 IS NOT NULL or MEMBERADDR.EMAIL2 IS NOT NULL)))
0
All Courses

From novice to tech pro — start learning today.