Subject Re: [firebird-support] Join three tables problem
Author H. S.
Post date 2014-11-27T23:36:35Z

Hi sir
Use this one

Select nameseller,sum(buys) as buys,sum(sells) as sells from(
select
Seller.NameSeller,
sum(buyfromseller.qty*buyfromseller.cost) as buys,
0.0 as sells
from
Seller
left join buyfromseller on buyfromseller.fkseller=seller.idseller
group by seller.nameseller
Union all
select
Seller.NameSeller,
0.0 as buys,
sum(sell.qtysell*sell.price) as Sells
from
Seller
left join buyfromseller on
left join sell on sell.fkseller=seller.idseller
group by seller.nameseller)

group by nameseller

Sent from Yahoo Mail on Android


From: ciccios@... [firebird-support] <firebird-support@yahoogroups.com>;
To: <firebird-support@yahoogroups.com>;
Subject: [firebird-support] Join three tables problem
Sent: Thu, Nov 27, 2014 9:48:38 AM

 

hi to all,
i have a problem i want join three tables, this is the structure (semplified)

Seller
IdSeller
NameSeller

BuyFromSeller
IdBuy
Qty
Cost
FkSeller

Sell
IdSell
QtySell
Price
FkSeller

i want know all the buy and the sell for seller (if there are)

i have tried
select
Seller.NameSeller,
sum(buyfromseller.qty*buyfromseller.cost) as buys,
sum(sell.qtysell*sell.price) as Sells,
from
Seller
left join buyfromseller on buyfromseller.fkseller=seller.idseller
left join sell on sell.fkseller=seller.idseller
group by
seller.nameseller

but it turn me doubles values for the sell sum, how can i resolve it?

thanks in advance.