Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Newbie SQL question

Re: Newbie SQL question

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Sun, 23 Feb 2003 16:20:24 -0800
Message-ID: <3E596548.F25F980A@exesolutions.com>


Richard Olivier wrote:

> Sorry but this is not class work, I merely stated the problem with a
> simplified
> example so that it would be easier for somebody with more SQL experience
> than
> me to understand the gist of the problem I'm trying to convey. My background
> is
> not with Oracle, but with another database that has it's own query language
> but is much
> syntactically different than Oracle SQL.
>
> I have looked at Oracle SQL books but none of the examples deal with
> effective dated
> outer joins.
>
> If you can provide a concrete example that works using this test data I
> would appreciate
> the help and my project would appreciate it too.
>
> Thanks.
> Richard.
>
> "DA Morgan" <damorgan_at_exesolutions.com> wrote in message
> news:3E580485.A11ACD0D_at_exesolutions.com...
> > Richard Olivier wrote:
> >
> > > I have two tables, a customer table and a region table, I would like to
> get
> > > a count of the number
> > > of customers in each region and I would like to display the region
> > > description
> > >
> > > Customer table:
> > > SETID CUSTID REGION
> > > SHARE 0001
> > > SHARE 0002
> > > SHARE 0003 A
> > > SHARE 0004 B
> > >
> > > Subcust Table (Region):
> > > SETID REGION DESCR EFFDT
> > > SHARE A Region A 01-JAN-1900
> > > SHARE B Region B 01-JAN-1900
> > >
> > > The SQL I am using is below. The result I am getting is as follows:
> > >
> > > Count Region Descr
> > > 1 A Region A
> > > 1 B Region B
> > >
> > > I would Like to see:
> > >
> > > Count Region Descr
> > > 2
> > > 1 A Region A
> > > 1 B Region B
> > >
> > > In other words, I need to see how many customers don't have a region
> > > assigned to them.
> > >
> > > Why is this outer join not returning all the records in the customer
> table?
> > >
> > > select distinct count(*),a.subcust_qual2,b.descr from ps_customer a,
> > > ps_subcust_q2_tbl b
> > > where a.setid = b.setid (+)
> > > and a.subcust_qual2 = b.subcust_qual2 (+)
> > > and b.effdt = (select max(effdt) from ps_subcust_q2_tbl
> > > where b.setid = setid
> > > and b.subcust_qual2 = subcust_qual2
> > > and effdt <= sysdate)
> > > group by a.subcust_qual2,b.descr
> > > order by a.subcust_qual2
> >
> > This is classword and you should identify it as such.
> >
> > You can do this in a number of ways using subqueries.
> >
> > To research the many possible ways to do this look at your class notes and
> open
> > your textbook.
> >
> > Daniel Morgan
> >
> >

To find records in one table but not in another ... an outer join is the wrong solution. The outer join will return those records that are in both. You should be thinking in terms of set theory. Something like:

SELECT field
FROM table_a
MINUS
SELECT field
FROM table_b

or something with NOT IN or NOT EXISTS.

If this doesn't help, try posting the actual problem, the name of the product you are more familiar with, and the SQL from that product that would solve the problem. No doubt you will get the help you need.

BTW: Simplified solutions, posted at a time that corresponds with college midterms and finals, often leads to the assumption that we are being asked to do someone's homework.

Daniel Morgan Received on Sun Feb 23 2003 - 18:20:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US