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

I think subselects, as shown by Jose Ostos and Paul Mercea, would be the
normal way to solve things like this. May I ask you why that is not an
alternative? Alternatives that I can think of are adding views or stored
procedures that does the sum for you, but such solutions sounds inferior
to simple subselects.

Set