>I have 2 tables. the 1st table called ITEM - keep detail about the goods to sales, 2nd called transaction keep sales transaction information for the item.
>
>How can using SQL syntax to list out every item with last transaction date and price?
>
>
>Thanks.

CREATE CURSOR Item (idSales I,idItem i,txtRow C(20))
FOR K=1 TO 10
INSERT INTO ITEM VALUES (m.k,RAND()*10,[sales]+STR(m.k))
NEXT
CREATE CURSOR Transaction (idTransaction I,idItem I,dDate D,yPrice Y)
FOR K=1 TO 100
	iItem=INT(RAND()*10)
	IF LOOKUP(Item.idSales,m.iItem,Item.idItem)>0
		INSERT INTO Transaction VALUES (m.k,m.iItem,DATE()+RAND()*5,RAND()*1000)
	ENDIF
NEXT

IF VERSION(5)>800
	SELECT * FROM Item I LEFT JOIN ;
		(SELECT * FROM Transaction T WHERE DTOS(dDate)+BINTOC(idTransaction)=(SELECT MAX(DTOS(dDate)+BINTOC(idTransaction)) FROM Transaction WHERE idItem=T.idItem)) T;
		ON T.idItem=I.idItem
ELSE	
	SELECT * FROM Item I LEFT JOIN Transaction T ON T.idItem=I.idItem;
		WHERE DTOS(dDate)+BINTOC(idTransaction)=(SELECT MAX(DTOS(dDate)+BINTOC(idTransaction)) FROM Transaction WHERE idItem=T.idItem)
ENDIF