>>>>>>Hi,
>>>>>>
>>>>>>I am trying to limit LEFT JOIN to select only one record. By design the child table should have only one record but I ran into a case where user had two records (wrong but it happens). And when two records are selected the entire resulting query is wrong. So, for example, say I have the following query:
>>>>>>
>>>>>>

>>>>>>select .... from Table1 left join Table2 on Table1.pk_field = Table2.pk_field
>>>>>>

>>>>>>
>>>>>>How can I change (if possible) the ON expression so that if Table2 has more than one record that matches the Table1.pk_field, only one is selected? (It does not matter to me which one). TIA.
>>>>>
>>>>>First of all, are you using VFP or SQL Server for this query? Secondly, do you want one record per each ID or you're selecting only for the particular ID (in this case Sergey's solution will work). For the 1 record per ID there is no good and simple VFP based solution available.
>>>>
>>>>I am trying to make this syntax work for either VFP or SQL Server. To answer the second part of your question, I want ONE RECORD PER EACH ID. If there is not solution, I will have to do more checking (before running this query) to be sure that the child table has only one record for the unique ID.
>>>
>>>One record per ID is a very simple query in SQL Server (with ROW_NUMBER() solution), but may be much harder in VFP (I can not quickly invent one).
>>
>>I need to change the design and make the ID in the table that is LEFT JOINed as primary key. I have not done it because of sloppy design initially. Now I am catching up. Thank you.
>
>In the meantime I figured out a query and I believe it should work in SQL Server as well:
>

>select Parent.*, Child1.*, Child2.* from Parent LEFT JOIN Child1 ... LEFT JOIN Child2 ..
>WHERE Child1.PK IS NULL OR Child1.PK = (select min(PK) from Child1 Ch1 where Ch1.ParentID = Parent.ID)

I will try this approach; hard for me to see if this will work or not by looking at code. On the other hand I am think if I add this "safety" code the result will slow down execution. I need to weigh pros and cons. Thank you.