Solved

SQL syntax - multiple joins

Posted on 2013-11-18
2
269 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
ID: 39657906
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
ID: 39659512
Perfect - thx!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help writing a query 6 77
ORA-00923: FROM keyword not found where expected 3 60
How do i get a breakdown of totals by age range? 10 22
sql server concatenate fields 10 32
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

776 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