SQL Question

Screwed up big-time today. I joined 2 tables as such, and got a wrong answer:

select
 sum(e.hours)
,sum(t.hours)
from comptime_earned e,

         comptime_taken t
where e.ssn = t.ssn (+)

As an example, one person had 1 row in earned of 8 hours, and taken had 3 rows of 2 hours each. When it summed, it tripled the 8 hours, resulting in over-paying 18 hours instead of 2. Ouch. I guess it did the sum function in a nested loops fashion for each of the 3 rows on the joined table.

Is there a way to prevent this? I was thinking about using an in-line view for the summation of the 2 tables. Is there a better way? Received on Mon Jan 24 2000 - 19:33:59 CST