>>I know that this topic was discussed before (I probably ask myself this question before) but I forgot how to do it.
>>
>>I have a parent and child tables. I need to show all records in the parent table and one column from child table. So that the resulting query has only one record per parent table. When I do LEFT JOIN the resulting query has as many records as there are records in the child table. I know (I think) it has to be done with derived column but I forgot how. Here is an example:
>>
>>

>>select table1.fld1, table1.fld2, table2.fld1 (... this is where I think the field table2.fld1 has to be created ) from table1
>>

>>
>>TIA
>
>If you want only one record, you need to specify how you get the child data. You can get just the first record by doing a subquery and SELECT TOP 1 ... ORDER BY someField, or you need to use an aggregate function like MAX, MIN, AVG, etc.

I cannot do aggregate functions (in this case) because I am modifying existing code. And I cannot use TOP 1 either. I think there is a way to add a (Select Fld1 from Table2 where...) Fld2add to make it work. But I am not sure about the syntax.
Thank you for your help.