Say I have the following data:

CREATE TABLE MAINTABLE(ID INTEGER);
CREATE TABLE SALESTABLE(ID INTEGER, SALES NUMERIC(15,4));
CREATE TABLE COSTSTABLE(ID INTEGER, COSTS NUMERIC(15,4));

INSERT INTO MAINTABLE(ID) VALUES(1);
INSERT INTO SALESTABLE(ID, SALES) VALUES(1, 10.00);
INSERT INTO SALESTABLE(ID, SALES) VALUES(1, 15.00);
INSERT INTO COSTSTABLE(ID, COSTS) VALUES(1, 5.00);
INSERT INTO COSTSTABLE(ID, COSTS) VALUES(1, 8.00);
INSERT INTO COSTSTABLE(ID, COSTS) VALUES(1, 1.00);

Now I run the following query:

select m.id, sum(s.sales), sum(c.costs)
from maintable m
left join salestable s on s.id = m.id
left join coststable c on c.id = m.id
where m.id = 1
group by m.id

My original hope was that I would return the results (1, 25.00, 14.00)
but I instead get (1, 75.00, 28.00). The joins take place between all
records including the costs/sales tables. Is there any way around this?
For what I'm doing, I really cannot use any sub select statements, nor
can I use two separate select statements. Is it possible to do this
with just one?

Thanks in advance,
Paul


[Non-text portions of this message have been removed]