Re: Outer Join Hell

Have you tried doing the subtotals in subqueries??

In article <7qji36$hkq$1_at_autumn.news.rcn.net>,   "A. Mills" <cici_at_erols.com> wrote:
> I am TRYING to create a query that returns totals based on a
household. A
> household is defined as people with the same last name, street, city,
state
> and zip code. I have this defined in a view. Bascially, what I am
trying
> to do is return the total number of households, the number of
registered
> households and the number of democratic households. (The registered
and
> democrat fields are defined in the same view using decode). I
thought by
> using an outer join on multiple instances of the same table, it would
split
> up the results correctly, but I am getting the same number for all
three
> fields. It is returning the number of registered democratic
> households....close, but no cigar. Any ideas would be very helpful.
>
> select mp.state,count(distinct(mp2.household)),
> count(distinct(mp3.household)),count(distinct(mp4.household)) from
> multi_purpose mp, multi_purpose mp2 , multi_purpose mp3,
multi_purpose mp4
> where mp.member_id=mp2.member_id(+) and mp.member_id=mp3.member_id(+)
and
> mp.member_id=mp4.member_id(+) and mp.zip_code='07010'
> and mp3.registered='Y'
> and mp4.democrat='Y'
> group by mp.state;
>
>

--
Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3700 <<<NOTE new number

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Sep 01 1999 - 12:19:58 CDT