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

As Rob correctly states, if you have multiple rows in Child table, you need to chose which of these multiple rows you want to bring over. What is the criteria you use to chose one row out of many?