Subject RE: [firebird-support] Join three tables problem
Author Leyne, Sean
> 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?

This is not a case where you want to use JOIN.

This is more what you want:

COALESCE( (SELECT SUM(buyfromseller.qty*buyfromseller.cost) FROM buyfromseller WHERE buyfromseller.fkseller=seller.idsellersum), 0) as buys,
COALESCE( (SELECT SUM(sell.qtysell*sell.price)) FROM Sell WHERE sell.fkseller=seller.idseller), 0) as Sells
FROM Seller
ORDER BY seller.nameseller