Re: Union Clause

On May 5, 9:58 am, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> 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

Thanks David, I'm sure one of those will work for me....I'll give them a try. Received on Mon May 05 2008 - 10:24:02 CDT