asked on 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
/* 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
Oracle Database
Last Comment
slightwv (䄆 Netminder)
use union in place of union all then tell us what is your output
also you can use what pratima_mcs: suggested
ASKER
@pratima_mcs:
No change in output
No change in output
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
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
ASKER
@ pratima_mcs:
Error gorup by X.EMAIL
SQL command not properly ended
Error gorup by X.EMAIL
SQL command not properly ended
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
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
ASKER
Error
ORDER BY X.MEMBERNAME
NOT A GROUP BY EXPRESSION
ORDER BY X.MEMBERNAME
NOT A GROUP BY EXPRESSION
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
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
ASKER
No error but output is still the same
AMARNATH KAMATH amarnathkamath@gmail.com
BHARATHI KAMATH amarnathkamath@gmail.com
AMARNATH KAMATH amarnathkamath@gmail.com
BHARATHI KAMATH amarnathkamath@gmail.com
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)
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)
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)
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)
ASKER
No luck so far in any of the queries
AMARNATH KAMATH amarnathkamath@gmail.com
BHARATHI KAMATH Amarnathkamath@Gmail.Com
AMARNATH KAMATH amarnathkamath@gmail.com
BHARATHI KAMATH Amarnathkamath@Gmail.Com
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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)
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)
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
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)
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)
I realize this is already closed but why have the union/joins/??? at all?
SELECT MEMBER.MEMBERNAME, nvl(MEMBERADDR.EMAIL1,MEMB
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)))
SELECT MEMBER.MEMBERNAME, nvl(MEMBERADDR.EMAIL1,MEMB
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)))
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