SQL Server Multiple Field Join

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
SCOTT78Technical AdvisorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SCOTT78Technical AdvisorAuthor Commented:
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
KeldenCommented:
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.
SCOTT78Technical AdvisorAuthor Commented:
Thanks, that does work but it returns 3 records. Is there a way I can have 1 record with the details I need?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

KeldenCommented:
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
...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
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
SCOTT78Technical AdvisorAuthor Commented:
Absolutely fantastic. Very impressive. Thank you for your help.. Brilliant!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.