Re: Newbie SQL question

"Richard Olivier" <rolivier56_at_earthlink.net> wrote in message news:M3Q5a.593$cD5.73373_at_newsread2.prod.itd.earthlink.net...
> 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
>

You have an implicit inner join by using a subquery. You could try to resolve that by changing the statement part for ps_subcust_q2_tbl into an inline view, but I think a better approach is trying to clean out the mess you have in that table.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address