>I think I have found a bug in VFP with its SQL engine. For some reason, whenever I execute the following SQL Select statement, I get an error: 'SQL: too many subqueries.' I checked the documentation and it says VFP is limited to two subqueries in one Select statement. Problem is, I can't find any subqueries in my select statement: all I see is a bunch of joins. I cannot find anything that tells me there is a limit to the number of joins that can be done in one select statement. So, is this a bug in the VFP SQL engine? Or am I missing something?
>
>Here is a simple Select statement I am trying to use: (And before you say anything, I can't split this into multiple queries as eventually I will need to add the WHERE clause so that I can filter based on anything in those related tables.)
>
>SELECT Lease.*,;
> Prospect.pro_idck,;
> Prospect.pro_name,;
> State.sta_name,;
> State.sta_postalid,;
> County.cou_name,;
> District.dis_idck,;
> District.dis_name,;
> Region.reg_idck,;
> Region.reg_name,;
> Type.cod_ name AS lse_type,;
> Status. cod _ name AS lse_status,;
> Instrument. cod _ name AS lse_instr,;
> Acquired. cod _ name AS lse_acquir,;
> Lessor. own _ idck AS lsr_idck,;
> Lessor. own _ name AS lsr_name,;
> Lessor. own _ namememo AS lsr_namememo,;
> Lessee. own _ idck AS lsee_idck,;
> Lessee. own _ name AS lsee_name,;
> Operator. own _ idck AS oper_idck,;
> Operator. own _ name AS oper_name,;
> Assignor. own _ idck AS asg_idck,;
> Assignor. own _ name AS asg_name,;
> Assignee. own _ idck AS asge_idck,;
> Assignee. own _ name AS asge_name,;
> RespParty. own _ idck AS resp_idck,;
> RespParty. own _ name AS resp_name,;
> Broker. own _ idck AS bro_idck,;
> Broker.own_ name AS bro_name;
> FROM Lease;
> LEFT JOIN Prospect;
> ON Prospect.pro_pk = Lease.lse_profk;
> LEFT JOIN Owner Lessor;
> ON Lessor.own_pk = Lease.lse_ownfk;
> LEFT JOIN Owner Lessee;
> ON Lessee.own_pk = Lease.lse_ownlsefk;
> LEFT JOIN Owner Operator;
> ON Operator.own_pk = Lease.lse_ownoperfk;
> LEFT JOIN Owner Assignor;
> ON Assignor.own_pk = Lease.lse_ownassfk;
> LEFT JOIN Owner Assignee;
> ON Assignee.own_pk = Lease.lse_ownasgefk;
> LEFT JOIN Owner RespParty;
> ON RespParty.own_pk = Lease.lse_ownrespfk;
> LEFT JOIN Owner Broker;
> ON Broker.own_pk = Lease.lse_ownbrofk;
> LEFT JOIN Region;
> ON Region.reg_pk = Lease.lse_regfk;
> LEFT JOIN District;
> ON District.dis_pk = Lease.lse_disfk;
> LEFT JOIN CodeMast Type;
> ON Type.cod_typefk + Type.cod_id = "LSE_TYPE " + Lease.lse_codtypefk;
> LEFT JOIN CodeMast Status;
> ON Status.cod_typefk + Status.cod_id = "LSE_STATUS" + Lease.lse_codstatfk;
> LEFT JOIN CodeMast Instrument;
> ON Instrument.cod_typefk + Instrument.cod_id = "LSE_INSTYP" + Lease.lse_codinstfk;
> LEFT JOIN CodeMast Acquired;
> ON Acquired.cod_typefk + Acquired.cod_id = "LSE_ACQCOD" + Lease.lse_codacqfk;
> LEFT JOIN State;
> ON State.sta_idck = Lease.lse_stafk;
> LEFT JOIN County;
> ON County.cou_stafk + County.cou_id = Lease.lse_stafk + Lease.lse_coufk;
> INTO CURSOR _Results
>
>Travis

---

I haven't counted your joins but seems they're above 10. I don't know the exact number but joins (subqueries) are limited to something like 9-11 (no documentation AFAIK).
Cetin