On May 5, 8:56=A0am, Mtek <m...@[EMAIL PROTECTED]
> wrote:
> On May 5, 8:46 am, Ed Prochak <edproc...@[EMAIL PROTECTED]
> wrote:
>
>
>
>
>
> > On May 5, 9:17 am, Mtek <m...@[EMAIL PROTECTED]
> wrote:
>
> > > Hi,
>
> > > This is my query:
>
> > > SELECT SUM(zcom) + SUM(ze) + SUM(zp)
> > > FROM
> > > =A0 =A0 (SELECT count(DISTINCT(email)) FROM customers_lookup) zcom
> > > =A0 =A0 UNION
> > > =A0 =A0 (SELECT count(DISTINCT(email)) FROM customers_lookup) ze
> > > =A0 =A0 UNION
> > > =A0 =A0 (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. =A0If 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.
>
> > 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 =A0the union operation and the join
> > operation?
>
> > hth,
> > =A0 ed
>
> Well, I figured that UNION removed duplicates. =A0So, I was trying to
> get a count of the email addresses that exist in all three tables. =A0I
> made a mistake in my query:
>
> SELECT SUM(zcom) + SUM(ze) + SUM(zp)
> FROM
> =A0 =A0 (SELECT count(DISTINCT(email)) FROM customers_lookup) zcom
> =A0 =A0 UNION
> =A0 =A0 (SELECT count(DISTINCT(email)) FROM customers_lookup_ze) ze
> =A0 =A0 UNION
> =A0 =A0 (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- Hide quoted text -
>
> - Show quoted text -
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(*) =3D 3;
EMAIL
----------------------------------------------------------------------------=
----
myemail
Of course you have other options:
select l.email
from customers_lookup l join customers_lookup_ze z on (z.email =3D
l.email) join customers_lookup_prim p on (p.email =3D 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
|
