You would use LEFT JOIN + ISNULL()

SELECT i.ItemId, uc.UserName, ISNULL(ua.UserName, 'Unassigned') ;
	FROM Item.i
		JOIN User uc ON uc.UserId = i.CreatorId
		LEFT JOIN User ua ON ua.UserId = i.AssigneeId

>Thanks for the quick and what looks like a great solution.
>Just a quick little follow-up question before I give you the stars :)
>
>Lets say an item was added to the Item table but it has not been assigned to anyone yet, i.e. AssigneeId = -1 (and UserId = -1 does not exist in the User Table). Currently an exception is thrown when the UserId isn't found. Is it possible to change the SELECT statement to not throw an exception and either return an AssigneeName = null or an empty string?
>
>Einar
>
>
>>You can use the same table multiple times by assigning alias to it.

>>SELECT i.ItemId,
>>           uc.UserName AS [Creator Name],
>>           ua.UserName AS [Assignee Name]
>>	FROM Item i
>>		JOIN User uc ON uc.UserId = i.CreatorId
>>		JOIN User ua ON ua.UserId = i.AssigneeId
>>