SQL problem

From: Joost Ouwerkerk <joost.ouwerkerk_at_sickkids.on.ca>
Date: Mon, 09 Nov 1998 16:09:00 GMT
Message-ID: <3647125c.5266864@resunix.sickkids.on.ca>

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 - 10:09:00 CST