Re: Union Clause

From: <fitzjarrell_at_cox.net>
Date: Mon, 5 May 2008 07:58:20 -0700 (PDT)
Message-ID: <00980aaa-f8f3-4e89-85b4-b91745681504@i76g2000hsf.googlegroups.com>

On May 5, 8:56 am, Mtek <m..._at_mtekusa.com> wrote:
> On May 5, 8:46 am, Ed Prochak <edproc..._at_gmail.com> wrote:
>
>
>
>
>
> > On May 5, 9:17 am, Mtek <m..._at_mtekusa.com> wrote:
>
> > > 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.
>
> > 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
>
> 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- 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(*) = 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 Received on Mon May 05 2008 - 09:58:20 CDT