Hi dmitry,

You can try to do this

SELECT ...
    FROM Table 1 
        LEFT JOIN Table2 ON Table1.pk = Table2fk
        LEFT JOIN Table2 as Table3 ON Table2.fk = Table3fk AND Table3.Pk > Table2.pk
    WHERE Table3.pk IS NULL

Please look carefully what I'm doing here. I'm only getting the most recent child record (with the highest pk value) of table2. All other records in Table2 will result in a lookup in Table3. Only Table2 records that will not result in a Table3 record will be returned because of the where clause.

I assumed that in your example you meant Table2.fk (in stead of pk), as there as a specific PK value is only applicable to one table.
I've used the construct above more than once to find the most recent child record in compex queries with multiple inner and left joins.

Walter,


>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.