What result did you expect?
If you want to get totals per po/item than add grouping to your select and take out price
SELE a.po,a.item,<b>SUM(a.qty),SUM(a.qty*b.price)</B> AS amt ;
FROM purchase a,stock b ;
WHERE a.item = b.item
<B>GROUP BY 1,2</B>
>I use following statement to select the records I want :
>
>SELE a.po,a.item,a.qty,b.price,a.qty*b.price AS amt ;
> FROM purchase a,stock b ;
> WHERE a.item = b.item
>
>However, when stock table has a item that supplied by 2 supplier, then my output will go wrong, for eg :
>
>purchase table
>po item qty
>123 a 10
>
>stock table
>item supplier price
>a abc 10
>a bcd 9
>
>
>then I get the result
>
>123,a,10,10,100
>123,a,10,9,90
>
>How can I solve this problem by single SQL SELECT statement.
>
>Thank In advance.
>I use following statement to select the records I want :
>
>SELE a.po,a.item,a.qty,b.price,a.qty*b.price AS amt ;
> FROM purchase a,stock b ;
> WHERE a.item = b.item
>
>However, when stock table has a item that supplied by 2 supplier, then my output will go wrong, for eg :
>
>purchase table
>po item qty
>123 a 10
>
>stock table
>item supplier price
>a abc 10
>a bcd 9
>
>
>then I get the result
>
>123,a,10,10,100
>123,a,10,9,90
>
>How can I solve this problem by single SQL SELECT statement.
>
>Thank In advance.
--sb--