I have a query with an inner join pulling data from two tables. One of the tables has several fields containing User IDs. This is a job table, so there is createdbyuserID, assignedtoUserID, etc. I'd like to append the existing query to pull in the names of these folks from the Users table, but I'm not sure how I would join the same table twice or even 4 times to pull names from the same set of user records.
My current query like so:
SELECT Customers.CustomerID, Customers.CustomerFirstName, Customers.CustomerLastName, Customers.CustomerEmail, Customers.CustomerPhoneCell, Customers.CustomerPhoneHome, Customers.OKContactLater, Jobs.JobAddress1, Jobs.JobAddress2, Jobs.JobCity, Jobs.JobState, Jobs.JobZipCode, Jobs.AcceptedPrice, Jobs.PaintStartDate, Jobs.PaintEndDate FROM Customers INNER JOIN Jobs ON Customers.CustomerID = Jobs.CustomerID WHERE PaintEndDate between 1/1/2013 and 12/31/2013
If I wanted to key on Jobs.CreatedByID to pull in Users.FirstName and Users.LastName and return that, then use Jobs.AssignedToID to pull in Users.FirstName and Users.LastName for that second different ID, how would I wrap my joins?