One other problem. What happens if the price changes? Most of us also include a price field in the Purchase table. Actually, I have and Invoice header table which has the invoice/po #, customer FK, invoice date, etc. A second table has all the invoice line items including invoice/po #, item_no [FK from stock table], item description, price, quantity, etc. I include the item description because it could change over time.

>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.