?
Solved

SQL Server Multiple Field Join

Posted on 2012-03-28
6
Medium Priority
?
308 Views
Last Modified: 2012-03-28
I have 2 tables with data. My Contract table stores an address along with 3 ID numbers. These 3 ID numbers reference names that are stored in another table. How can I perform a join to retrieve the details?

I have tried an inner join as below but this only gives me 1 of the 3 fields I need:

'SELECT c.*, s.* FROM tbl_ContractDetails c INNER JOIN tbl_Staff s ON c.ContractManagerID = s.ID WHERE JobNo = 807'

I need to join the 2 other fields from the Staff table to the query.

Any help is appreciated
Thanks
0
Comment
Question by:SCOTT78
  • 3
  • 2
6 Comments
 

Author Comment

by:SCOTT78
ID: 37775558
This is the join I need but doesnt work:

SELECT c.*, s.* FROM tbl_ContractDetails c INNER JOIN tbl_Staff s ON c.ContractManagerID = s.ID AND c.SurveyorID = s.ID AND c.SiteManagerID = s.ID
WHERE JobNo = 807
0
 
LVL 2

Expert Comment

by:Kelden
ID: 37775568
If you need all 3 persons, change from AND to OR

SELECT c.*, s.* FROM tbl_ContractDetails c INNER JOIN tbl_Staff s ON c.ContractManagerID = s.ID OR c.SurveyorID = s.ID OR c.SiteManagerID = s.ID
WHERE JobNo = 807

also you should write
c.JobNo or s.JobNo so it's obvious from which table JobNo is.
0
 

Author Comment

by:SCOTT78
ID: 37775606
Thanks, that does work but it returns 3 records. Is there a way I can have 1 record with the details I need?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 2

Accepted Solution

by:
Kelden earned 148 total points
ID: 37775923
Yes, you can.

SELECT c.*, s1.*, s2.*, s3.*
FROM tbl_ContractDetails c
INNER JOIN tbl_Staff s1 ON c.ContractManagerID = s.ID
INNER JOIN tbl_Staff s2 ON c.SurveyorID = s.ID
INNER JOIN tbl_Staff s3 ON c.SiteManagerID = s.ID
WHERE JobNo = 807

You should only use some fields from tbl_Staff as it gets harder to read from it.
Better would be to use e.g.
SELECT c.*, s1.NAME as ContractManger_NAME, s2.NAME as Surveyor_NAME, s3.NAME as SiteManager_NAME
...
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 152 total points
ID: 37777015
And you probably want to use LEFT OUTER JOIN rather than INNER JOIN, so you don't exclude everything just because the value is not in one of the tables:


LEFT OUTER JOIN tbl_Staff s1 ON c.ContractManagerID = s.ID
LEFT OUTER JOIN tbl_Staff s2 ON c.SurveyorID = s.ID
LEFT OUTER JOIN tbl_Staff s3 ON c.SiteManagerID = s.ID
0
 

Author Closing Comment

by:SCOTT78
ID: 37777162
Absolutely fantastic. Very impressive. Thank you for your help.. Brilliant!!
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

807 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