You need to add the inner join to the correlated subquery. It must be a standalone query with all parts defined.
-----Original Message----- From: "MB Software Solutions" <[EMAIL PROTECTED]> To: "Profox" <profox@leafe.com> Sent: 3/1/07 11:24 Subject: Need help with VFP9 SQL JOIN subquery I know the new functionality in VFP9 SQL is cool like Oracle had where you can defined joins to subqueries, but I seem to have a flaw in my code. (see below). select v1.iid, ; v1.nMatlID, ; v1.nVendorID, ; d1.nUnitCost, d1.dActive, ; v1.cVendorSKU, ; lu1.cCompany; FROM eiwdb!luMaterialVendors v1 ; JOIN eiwdb!luCompanies lu1 ; ON lu1.iid = v1.nMatlID ; join (select iid, nunitcost, max(dActive) from eiwdb!luMaterialVendors v2 where v2.nMatlID = v1.nMatlID) d1 on v1.iid = d1.iid ; WHERE v1.nMatlID = ?viMatlID ; ORDER BY lu1.cCompany I'm trying to get the latest price record. The luMaterialVendors table holds the pricing history for a given material to a vendor. When I try this in ViewEditor 3.0.126, it tells me that alias v1 is not found. I've defined "v1" immediately in the FROM clause. Ideas? tia! --Michael _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.