Solved

SQL Server Multiple Field Join

Posted on 2012-03-28
6
283 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

785 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