Hi!

1. Split joining criteria from expressions to use "AND". This way you speed up query and will simplify indexes when you will require them for optimization.

2. Do not split query to several queries. This is because you designed them in a model when "(nameplat is parent for visual) is parent for liquid", instead of "nameplat is parent for visual AND is parent for liquid", as you needed. You should make all joins in a single query and clearly define criteria for joining. Something like:

SELECT nameplat.custid, nameplat.upsino, datinp, sub_name, ;
    co_equipno, serial_no, liquid, equip_type, leaks, recser ;
  FROM nameplat ;
    LEFT JOIN visual ON nameplat.custid = visual.custid AND nameplat.upsino = visual.upsino ;
    LEFT JOIN liquid ON nameplat.custid = liquid.custid  AND nameplat.upsino = liquid.upsino AND ;
      visual.datinp = liquid.puldat ;
  WHERE nameplat.custid = gcCustid ;
    AND datinp >= ldStartDate AND datinp <= ldEndDate ;
    AND liquid.puldat >= ldStartDate AND liquid.puldat <= ldEndDate ;
  INTO CURSOR curNamevis ;
  ORDER BY nameplat.upsino, datinp

Finally, about the problem related to the too many records: If you have 2 or nore records that match condition "nameplat.custid = liquid.custid AND nameplat.upsino = liquid.upsino AND visual.datinp = liquid.puldat" at BOTH sides (in both visual and liquid tables), you require to define additional citeria for joining them to make one of the sides have only one record that match this criteria, otherwise you'll get a full joing records in the context of mentioned joining criteria.

HTH.


>Ok, I have been trying all day now and have searched long enough. This should be simple but I just don't get it.
>
>I have six Foxpro 2.6 tables that I need to retrieve data from to create a cursor for a report. The tables are Visual, Liquid, Dga, Pcb, Furan and Nameplat. All the tables have commom fields named "custid" and "upsino". The first five tables all have a date field named either "puldat" or "datinp". You could say the Nameplat is the parent table if a relationship were setup.
>
>The criteria for retrieving data will be a custid, a date range between which "puldat" or "datinp" will have to fit, and either all upsinos for a the selected custid or a selective range of upsinos.
>
>I need to combine various fields from each of the tables for each record and include any records that fit in the date range and/or upsino criteria, even if they don't have a related record in the other tables. (i.e., only a Visual record exists for a certain UPSINO but no data for that one exists in any of the other child tables - Liquid, Dga, Pcb, or Furan.)
>
>I have started out by just trying to combine just two of the tables, Visual and Liquid first. I get the proper records selected from the Liquid, however records from Visual that do not have a matching record in Liquid are excluded. I have tried using INNER, LEFT, and RIGHT joins in my SELECT statements joining on custid, upsino and DTOS(puldat). In my results I either get the exlclusions mentioned above or I get way to many records in the result set because each record is matched with every record in the other table.
>
>How do I properly SELECT and combine data from more than two tables while including all records from each of the tables that meet the criteria? Below are some of my attempts:
>
>First I combine nameplat and visual data:
>

>SELECT nameplat.custid, nameplat.upsino, datinp, sub_name, ;
>co_equipno, serial_no, liquid, equip_type, leaks, recser ;
>FROM nameplat ;
>INNER JOIN visual ON (nameplat.custid + nameplat.upsino = ;
>visual.custid + visual.upsino) ;
>WHERE nameplat.custid = gcCustid AND datinp >= ldStartDate AND ;
>datinp <= ldEndDate ;
>INTO CURSOR curNamevis ORDER BY nameplat.upsino, datinp
>

>
>Then I combine that with liquid data:
>

>SELECT curNamevis.upsino, liquid.upsino, datinp, liquid.puldat, sub_name, co_equipno, serial_no, liquid, ;
>equip_type, leaks, recser, class, rec_servic ;
>FROM curNamevis ;
>LEFT JOIN liquid ON ALLTRIM(curNamevis.custid + curNamevis.upsino + DTOS(curNamevis.datinp)) = ;
>ALLTRIM(liquid.custid + liquid.upsino + DTOS(liquid.puldat)) ;
>WHERE liquid.puldat >= ldStartDate AND liquid.puldat <= ldEndDate ;
>AND ALLTRIM(curNamevis.upsino + DTOS(curNamevis.datinp)) = ALLTRIM(liquid.upsino + DTOS(liquid.puldat)) ;
>INTO CURSOR curLiq ORDER BY liquid.upsino, liquid.puldat
>

>
>Thank you so much for your help.