If subselect is not allowed , I would try some union:



select m.id, sum(s.sales) ,sum(0.00) costs

from maintable m join salestable s on s.id = m.id

group by m.id

union

select m.id, sum(0.00) , sum(c.costs)

from maintable m join coststable c on c.id=m.id

group by m.id

but this will not give you just one record but 2..



You need to sum again .



I don't understand why this is not working:

A)

select m.id, sum(s.sales) sales,sum(0) costs

from maintable m join salestable s on s.id = m.id

group by m.id

union

select m.id, sum(0) sales, sum(c.costs) costs

from maintable m join coststable c on c.id=m.id

group by m.id



but working with sum(0.00)



Regards,

Paul



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvaer
Sent: Tuesday, February 27, 2007 10:04 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] question about joins



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





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