sql - join three tables in firebirdsql -
i have problem: i'd join 3 table in order values, example:
sellers idsellers nameseller stock idstock qtystock cost fkseller sell idsell qtysell price fkseller
i want out total amount of stock
, sell
tables each seller
u have tried following query doubles values each sell:
select sellers.nameseller, sum(stock.qty*stock.cost)as stocks, sum(sell.qty*sell.price) sells sellers left join stock on stock.fkseller=sellers.idseller left join sell on sell.fkseller=sellers.idsellers group sellers.nameseller
the firebirdsql release in use 2.1
thanks in advance.
the problem if have example 2 in sell , 2 in stock. first join produce 2 records, second join create 2*2 records. instead should union data , join once, or use select expression produce sums.
using select expression:
select sellers.nameseller, (select sum(stock.qty * stock.cost) stock stock.fkseller=sellers.idseller) stocks, (select sum(sell.qty * sell.price) sell sell.fkseller=sellers.idsellers) sells sellers
using union:
select sellers.nameseller, sum(stocksell.stockqty * stocksell.stockcost) stocks, sum(stocksell.sellqty * stocksell.sellprice) sells sellers left join ( select fkseller, qty stockqty, cost stockcost, 0 sellqty, 0 sellprice stock union select fkseller, 0 stockqty, 0 stockcost, qty sellqty, price sellprice sell ) stocksell on stocksell.fkseller=sellers.idseller group sellers.nameseller
Comments
Post a Comment