SQL Server JOIN - displaying two joined values in one query that map to one other table?

Question

Here is an example of the data set I am working with. I am trying to write a query that will allow me to request a row from the items table, and display the User Name from the users table in place of the user ids:

Table 1 - users table

User ID | User Name 
--------------------
12      | Fred
13      | Ted
14      | Ned

Table 2 - items Table

Item ID | Submitted User | Assigned User
----------------------------------------
234     | 12             | 14
345     | 12             | 13
456     | 14             | 12

This is as far as I can get, which returns one properly labled username:

SELECT users.[user name] AS [Submitted User] FROM items 
JOIN users ON items.[Assigned User] = users.[User ID]
WHERE items.[Item ID] = '234'

The problem with this is that while ONE field works, I need to get both the submitted user AND the assigned user. I am trying to do it in one query... I have a feeling it can be done but I just don't know exactly how. If I try to assign the fields more than once I get an error about correlation. Here is an example of what I tried that gives a correlation error:

SELECT users.[user name] AS [Submitted User], users.[user name] AS [Assigned User] FROM items 
JOIN users ON items.[Submitted User] = users.[User ID]
JOIN users ON items.[Assigned User] = users.[User ID]
WHERE items.[Item ID] = '234'

This is exactly what I'm trying to do:

SELECT 
  items[Submitted User].users.[user name] AS [Reported User],
  items[Assigned User].users.[user name] AS [Assigned User] 
WHERE items.[Item ID] = '234'</pre>

This is the response I'm trying to get:

Submitted User | Assigned user
Fred           - Ted

Answer 1

You want to alias your JOIN table so that you can join against it twice. See below:

SELECT 
    submitted.[user name] AS [Submitted User], 
    assigned.[user name] AS [Assigned User] 
FROM items 
    LEFT JOIN users submitted ON items.[Submitted User] = submitted.[User ID]
    LEFT JOIN users assigned ON items.[Assigned User] = assigned.[User ID]
WHERE items.[Item ID] = '234'

Some other suggestions:

• Table 2's column names should be Submitted User ID, Assigned User ID to make it more explicit that you're joining IDs.
• You might want to make your ID fields INT. It makes for faster joins and less space.
• Don't forget to build indices on the ID fields on both tables so the JOIN can take advantage of them.
• Thanks astander, changed join to LEFT JOIN since you probably want to see the results