I've got a SELECT command that I'm using in ADO.NET in a VB.NET application. It works against SQL Server 2000, but I need it to work against VFP Free tables as well using the VFP OleDb driver. Here's the SQL:

SELECT h.pid_owner, h.ord_cod,
       (SELECT SUM(d.itm_prcus * d.qty_ord)
          FROM c:\qn\datatemp\qn_dtl d
          WHERE d.pid_owner = h.pid_owner
            AND d.ord_cod = h.ord_cod) AS OrderTotal,
       (SELECT SUM(i.itm_prcus * i.qty_sent)
          FROM c:\qn\datatemp\qn_inv i
          WHERE i.pid_owner = h.pid_owner
            AND i.ord_cod = h.ord_cod) AS InvoiceTotal
  FROM c:\qn\datatemp\qn_head h
  ORDER BY h.pid_owner, h.ord_cod

Basically, I have a master table (qn_head) and two child tables (qn_dtl and qn_inv). The child tables link to the master table on the fields pid_owner and ord_cod. I need one row for each row in qn_head with all the fields from qn_head, plus one field that sums the qn_dtl records for each qn_head record, plus one field that sums the qn_inv records for each qn_head record. For a given qn_head record there could be 0 or more qn_dtl record, and 0 or more qn_inv records. A given qn_head record may have a different number of qn_dtl records than it has qn_inv records.

VFP complains about invalid use of subquery with the above SQL, and I assume that's because VFP doesn't allow subqueries in the SELECT fields like I'm doing. If I only had one child table, this would be simple. But since I have 2 child tables, and I'm using free VFP tables, not a DBC (no views), and ADO.NET doesn't support dooing a SQL against a DataSet, I'm not sure how to get this to work.

-----
Lee Perkins