Union Clause

#21

Mtek wrote:

Quote:

Hi,

This is my query:

SELECT SUM(zcom) + SUM(ze) + SUM(zp)
FROM
(SELECT count(DISTINCT(email)) FROM customers_lookup) zcom
UNION
(SELECT count(DISTINCT(email)) FROM customers_lookup) ze
UNION
(SELECT count(DISTINCT(email)) zp FROM customers_lookup) zp);

Basically I want to get a count from the three tables where the email
address appears in ALL THREE tables. If an email does not exist in
all three tables, then I do not want it counted.......

Use INTERSECT, not UNION.

Or use a normal inner join...

select count(distinct a.email)
from
customers_lookup a
inner join customers_lookup_ze b on b.email = a.email
inner join customers_lookup_prim c on c.email = b.email

--
Peter

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Union Clause

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Union Clause - 05-06-2008 , 01:04 AM