Re: Newbie SQL question

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 Received on Sat Feb 22 2003 - 17:15:17 CST