Re: SQL problem

From: Jason Jay Weiland <archduke_at_uclink4.berkeley.edu>
Date: Mon, 09 Nov 1998 10:27:09 -0800
Message-ID: <364733E6.B5F6AA60@uclink4.berkeley.edu>

Joost,

     You are getting a cartesian product since you are not restricting the join between the "interest" and "paymnet" tables. One solution would be to calculate the sum of the payment table in an in-line view. Try this one:

SELECT pe.name, max(int.code), p_sum.amount FROM interest int, person pe,

       (  SELECT pe_sum.idnumber, sum(pa.amount) amount
            FROM person pe_sum, payment pa
           WHERE pe_sum.idnumber = pa.idnumber
        GROUP BY pe_sum.idnumber) p_sum

WHERE pe.idnumber=int.idnumber(+)
AND pe.idnumber=p_sum.idnumber(+)
GROUP BY pe.name, p_sum.amount

...and just to nitpick, make sure you only have one "people" table (you described 'person' and selected from 'people' :)

Jay!!!

P.S> Be careful with your column and alias

     choice; "in" and "date" are generally
     reserved words.

Joost Ouwerkerk wrote:

> Given 3 tables: person (name, idnumber),
> interest (code,idnumber,transnum),
> payment (amount, date, idnumber, transnum)
>
> When I join all three tables, eg:
>
> SELECT pe.name, max(in.code), sum(pa.amount)
> FROM payment pa, interest in, people pe
> WHERE pe.idnumber=in.idnumber(+)
> AND pe.idnumber=pa.idnumber(+)
> ;
>
> The payment amount sum is not correct -- it is multiplied by the
> number of interest records that the person has. How can I avoid this
> problem?
>
> Joost Ouwerkerk.
Received on Mon Nov 09 1998 - 12:27:09 CST