Fabio,
Your solution got solution stars in my book :)
Thanks for confirming that the two solutions proposed are equivalent.
You are correct your solution is somewhat easier to read, but Sergey's solution wasn't that hard to read either.
Einar
>>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
>>>
Semper ubi sub ubi.