>>>>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.
>>>
>>>SELECT TOP 1 ... ORDER BY Table1.pk_field
>>
>>I cannot change the SELECT part of my query (if understand your suggestion correctly). That is, the SELECT part (before FROM) involves many fields. Unless you mean that SELECT TOP 1 can be part of the LEFT JOIN expression.
>>Thank you.
>
>
>He means
>
>select top 1 * from myTable LEFT JOIN myOtherTable on ... where ID = SomeID
Thank you for clarifying. In my case this will not work. Because I was showing the case simplified. The resulting query can have more than one record (there is another LEFT JOIN that I didn't mention). But if this LEFT JOIN that I mentioned pulls not 1 but 2 records, then the resulting query has double the number of records.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham