Solved

SQL Server Multiple Field Join

Posted on 2012-03-28
6
292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 2

Accepted Solution

by:
Kelden earned 37 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 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 38 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

705 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