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.......

I think I'm barking up the wrong tree with the query above......

John.

On May 5, 9:17 am, Mtek <m... (AT) mtekusa (DOT) com> wrote:
But you show only one table in this query, customers_lookup.

Here's a hint in the form of a question:
What is the difference between the union operation and the join
operation?

hth,
ed

On May 5, 9:17 am, Mtek <m... (AT) mtekusa (DOT) com> wrote:
But you show only one table in this query, customers_lookup.

Here's a hint in the form of a question:
What is the difference between the union operation and the join
operation?

hth,
ed

On May 5, 9:17 am, Mtek <m... (AT) mtekusa (DOT) com> wrote:
But you show only one table in this query, customers_lookup.

Here's a hint in the form of a question:
What is the difference between the union operation and the join
operation?

hth,
ed

On May 5, 9:17 am, Mtek <m... (AT) mtekusa (DOT) com> wrote:
But you show only one table in this query, customers_lookup.

Here's a hint in the form of a question:
What is the difference between the union operation and the join
operation?

hth,
ed

On May 5, 8:46 am, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:
Well, I figured that UNION removed duplicates. So, I was trying to
get a count of the email addresses that exist in all three tables. I
made a mistake in 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) ze
UNION
(SELECT count(DISTINCT(email)) zp FROM customers_lookup_prim) zp);

There are the three tables.

So, I only want to include emails which are in all three tables for my
counts....

Thanks for the reply.

John

On May 5, 8:46 am, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:
Well, I figured that UNION removed duplicates. So, I was trying to
get a count of the email addresses that exist in all three tables. I
made a mistake in 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) ze
UNION
(SELECT count(DISTINCT(email)) zp FROM customers_lookup_prim) zp);

There are the three tables.

So, I only want to include emails which are in all three tables for my
counts....

Thanks for the reply.

John

On May 5, 8:46 am, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:
Well, I figured that UNION removed duplicates. So, I was trying to
get a count of the email addresses that exist in all three tables. I
made a mistake in 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) ze
UNION
(SELECT count(DISTINCT(email)) zp FROM customers_lookup_prim) zp);

There are the three tables.

So, I only want to include emails which are in all three tables for my
counts....

Thanks for the reply.

John

On May 5, 8:46 am, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote:
Well, I figured that UNION removed duplicates. So, I was trying to
get a count of the email addresses that exist in all three tables. I
made a mistake in 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) ze
UNION
(SELECT count(DISTINCT(email)) zp FROM customers_lookup_prim) zp);

There are the three tables.

So, I only want to include emails which are in all three tables for my
counts....

Thanks for the reply.

John

On May 5, 8:56*am, Mtek <m... (AT) mtekusa (DOT) com> wrote:
And this invocation of UNION won't provide the results you seek as
you generate a COUNT before you can eliminate the duplicates. Setting
up an example:

SQL> create table customers_lookup (
2 email varchar2(128),
3 customer varchar2(40)
4 );

Table created.

SQL> create table customers_lookup_ze as select * From
customers_lookup;

Table created.

SQL> create table customers_lookup_prim as select * From
customers_lookup;

Table created.

SQL> insert all
2 into customers_lookup
3 values('myemail', null)
4 into customers_lookup
5 values('youremail', null)
6 into customers_lookup
7 values('theiremail', null)
8 select * from dual;

3 rows created.

SQL> commit;

Commit complete.

SQL> insert into customers_lookup_ze
2 select * From customers_lookup
3 where email not like 'your%'
4 /

2 rows created.

SQL> commit;

Commit complete.

SQL> insert into customers_lookup_prim
2 select * from customers_lookup
3 where email not like 'their%'
4 /

2 rows created.

SQL> commit
2 /

Commit complete.

One possibility of a working query, using UNION ALL, might be:

SELECT email
FROM
(SELECT email FROM customers_lookup
UNION ALL
SELECT email FROM customers_lookup_ze
UNION ALL
SELECT email FROM customers_lookup_prim)
group by email
having count(*) = 3;

EMAIL
--------------------------------------------------------------------------------
myemail


Of course you have other options:

select l.email
from customers_lookup l join customers_lookup_ze z on (z.email =
l.email) join customers_lookup_prim p on (p.email = z.email);

EMAIL
--------------------------------------------------------------------------------
myemail

Or:

select email
from customers_lookup
where email in (select email from customers_lookup_ze
where email in (select email from
customers_lookup_prim));

EMAIL
--------------------------------------------------------------------------------
myemail

But, your current query won't produce anything:

SQL> SELECT SUM(zcom) + SUM(ze) + SUM(zp)
2 FROM
3 (SELECT count(DISTINCT(email)) FROM customers_lookup) zcom
4 UNION
5 (SELECT count(DISTINCT(email)) FROM customers_lookup_ze) ze
6 UNION
7 (SELECT count(DISTINCT(email)) zp FROM customers_lookup_prim)
zp);
(SELECT count(DISTINCT(email)) FROM customers_lookup_ze) ze
*
ERROR at line 5:
ORA-00933: SQL command not properly ended

The closest example to your query which actually works is:

SELECT SUM(l.zcom) + SUM(z.ze) + SUM(p.zp)
FROM
(SELECT count(DISTINCT(email)) zcom FROM customers_lookup) l,
(SELECT count(DISTINCT(email)) ze FROM customers_lookup_ze) z,
(SELECT count(DISTINCT(email)) zp FROM customers_lookup_prim) p;

and it produces the following result from my data:

SUM(L.ZCOM)+SUM(Z.ZE)+SUM(P.ZP)
-------------------------------
7
which isn't usable in my estimation as it provides nothing in the way
of information regarding how the data satisfies your criteria (same
email in all three tables).

Look at the three working examples I've provided and work from that
point forward.


David Fitzjarrell