Solved

SQL syntax - multiple joins

Posted on 2013-11-18
2
261 Views
Last Modified: 2013-11-19
Hi,

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

Open in new window


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?

Thank you

Bill
0
Comment
Question by:billium99
2 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
Comment Utility
Join the query to the Users Table twice - aliasing it the first time as Users1 and the second time as Users2:

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, Jobs.CreatedbyID, Users1.First_name, Users2.LastName, Jobs.AssignedToID, Users2.FirstName, Users2.LastName
FROM Customers 
INNER JOIN Jobs ON Customers.CustomerID = Jobs.CustomerID 
INNER JOIN Users Users1 on Jobs.CreatedByID = Users1.UserID
INNER JOIN Users Users2 on Jobs.AssignedToID = Users2.UserID
WHERE PaintEndDate between 1/1/2013 and 12/31/2013

Open in new window

0
 
LVL 1

Author Closing Comment

by:billium99
Comment Utility
Perfect - thx!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now