You have no mention what version of FB have.

You can try for FB 1.5.3 and 2.0



select m.id, (Select sum(s.sales) from salestable s where s.id = m.id)
sales, (select sum(c.costs) from coststable c where c.id=m.id) costs

from maintable m

*where m.id = 1* - delete this

group by m.id



If you are using where m.id=1 is no logic to have group by.



Regards,

Paul



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Paul R. Gardner
Sent: Tuesday, February 27, 2007 6:03 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] question about joins



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]