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