Sergey,
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
>

>>I've got a bad feeling that this is not possible, but I think it is worth asking anyways.
>>I've got a User table with two fields (just to make it simple): UserId and UserName
>>I've got an Item table with 3 fields (again to make it simple): ItemId, CreatorId and AssigneeId.
>>
>>CreatorId and AssigneeId are foreign keys into the User table.
>>
>>Now is there any way I can write a SELECT SQL statement to get me the following result:
>>

>>ItemId    Creator Name    Assignee Name
>>------    ------------    -------------
>>1         Emma            Einar
>>2         Kevin           Bonnie
>>3         John            Jane
>>

>>
>>Using JOIN I can for sure get one of the names from the User table, but in this case I kind of need a doubble JOIN into the same table.
>>
>>
>>Something tells me that this isn't possible but I would love to be proven wrong (and lern something new).
>>
>>Einar