Solved

SQL Server Multiple Field Join

Posted on 2012-03-28
6
288 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Backup & Restore 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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 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