This is the exact copy of the query I have:
cSqlCommand = "select C1.*, C2.LASTNAME, C2.FIRSTNAME, C2.TELEPHONE1 from COMPANY C1 " + ;
" LEFT JOIN ( SELECT TOP 1 LASTNAME, FIRSTNAME, TELEPHONE1, PEOPLE.COMP_PK FROM
PEOPLE C3 " + ;
"WHERE C1.COMP_PK = C3.COMP_PK ORDER BY ORDERNO ) C2 ON C1.COMP_PK = C2.COMP_PK"
>Can you post your query?
>
>>When I tried your first (top) suggestion (in a bit modified form but almost the same as you have it) I get error:
>>
>>"The multi-part identifier 'C1.COMP_PK' could not be bound"
>>
>>What does it mean?
>>
>>>Try
>>>
>>>select C1.*, C2.LASTNAME
>>> from COMPANY C1
>>> LEFT JOIN ( select top 1 LastName, comp_pk from
>>> CONTACT c3 WHERE C1.comp_pk = C3.comp_pk order by OrderNo ) C2 on C1.comp_pk = C2.comp_pk
>>>
>>>
>>>
>>>select C1.*,
>>> ( select top 1 LastName from CONTACT c3 WHERE C1.comp_pk = C3.comp_pk order by OrderNo ) AS LASTNAME
>>> from COMPANY C1
>>>
>>>
>>>
"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
