>Fabio,
>Thank you for your reply. Stars have already been issued to Sergey,
This is a UT issue, because often more solutions exist.
>but your proposed solution seems to make a lot of sense too. (Help star is comming your way)
>If you checked out Sergey's solution, would you care to comment on performance advantages that one solution has over the other?
>
>To me it seems like Sergey's solution would execute faster than your, but maybe behind the scenes the two solutions are equivalent.
>
>
>Einar
>
On SQl Server the two solutions are equivalent.
With a single field this is more readable.
With two or more fields the SQL syntax is insufficient
SELECT
...
,(SELECT field1,field2 FROM ...)
and you have to use INNER JOIN or LEFT JOIN.
But then, to understand that the JOIN doesn't remove or it adds rows
you needs to know the meaning of the fields of the clause ON.
>
>>Single field lookup:
>>
>>SELECT i.ItemId,
>> ISNULL((SELECT UserName FROM User WHERE UserId = i.CreatorId),'Unknown') AS [Creator Name],
>> ISNULL((SELECT UserName FROM User WHERE UserId = i.AssigneeId),'Unknown') AS [Assignee Name]
>> FROM Item i
>>