You may want to look at http://msdn.microsoft.com/vfoxpro/letters/02102004/


>Craig,
>
>I forgot to answer this question...
>
>A good example is scalar subqueries. Many times I've had one-to-many relationships involving multiple tables, such as a Job Master table and then a Job Labor Table with many time records for each job and a Job Material table with many materials records for each job.
>
>When I want to query to bring back a sum of hours and materials for each job, I can't do an outer join against both tables in 1 query, because I'll get double-counting.
>
>The following query runs fine in SQL Server, but not in VFP:
>
>SELECT JobMast.JobNumber, ;
>(SELECT sum(JobLabor.LaborTime) FROM JobLabor WHERE JobLabor.JOBNUMBER
>= JOBMAST.JOBNUMBER) AS LaborTime, ;
>(SELECT SUM(material.amount) from material where material.jobnumber
>= jobmast.jobnumber) as material ;
>from jobmast group by JOBMAST.JOBNUMBER
>
>I get 'invalid use of subquery' when I try it in VFP, becuase VFP does not support SELECT as a column. My alternatives are to build two queries, or use UDFs. I've usually opted for the former, but I wish I could accomplish things like this in one line of code.
>
>Kevin