Try

select m.id, (SELECT sum(s.sales) FROM salestable s WHERE s.id = m.id)
AS Sales,
(SELECT sum(c.costs) FROM coststable c WHERE c.id = m.id) AS Costs

In the sample you put what is processed is the followwin

ID SALES COST
1 10 5
1 10 8
1 10 1
1 15 5
1 15 8
1 15 1

Giving you the result you mentioned. The new query should traverse each
table independently because there really is no join.

Hope this helps,

Jose Ostos

Paul R. Gardner wrote:

>
> 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]
>
>

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