>>>>Hi,
>>>>
>>>>I sense this there is something real simply I am missing in the following SQL Select but I don't seem to be understand it.
>>>>
>>>>I am trying to select one record from a Company table and one record from a Contact table (related to Company by column COMP_PK). But I get a NULL in column LASTNAME. Here is my SQL Select:
>>>>
>>>>

>>>>select C1.*, C2.LASTNAME from COMPANY C1 LEFT JOIN ( select top 1 LastName, comp_pk from 
>>>>CONTACT order by OrderNo ) C2 on C1.comp_pk = C2.comp_pk where 1=1
>>>>

>>>>
>>>>What is wrong with my SQL Select?
>>>
>>>Have you considered using a function to get the Contact information? That would avoid the headache of the join and would let you re-use that code for other queries.
>>
>>I am not sure what you mean by "using a function". If you are referring to a VFP function than I can't. I have to pass the entire SQL Select to SQL Server and passing VFP function won't work.
>
>Dmitry,
>
>This is what Linda meant:
>
>

>create function GetContactName (@Comp_pk int) return varchar(30)
>as
>  begin
>   declare @ContactName varchar(30)
>    select top 1 @ContactName = LastName from Contact where Comp_pk = @Comp_pk Order by OrderNo
>   return @ContactName
> end
>

>And to use it
>
>

>select C1.*, dbo.GetContactName(C1.Comp_pk) as Contact from Company

Ok. Thanks.