Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Oct 2, 1998

SQL Query

Question:
I am a beginner in SQL and can't figure out how to do this: I have two tables with three fields in the row of table1 referencing a master table in an n-1 relation as follows:

JobMast                          ContractorMast
=======                          ==============
JobID                                  ContrID
JobDescription                 -----1> ContrName 
GenContractorID    n ---------/ | |
ElecContractorID   n -----------/ |
MechContractorID   n -------------/

I want a query that returns a JobMast recordset, but replaces the contractor IDs with the full name:
JobDesc|GenContractor|ElecContractor|MechContractr
The closest I've gotten is using INNER JOIN to get the result as separate records:

JobDesc|GeneralContractor
JobDesc|ElecContractor
JobDesc|MechContractor

Answer:
You need to refer to the ContractorMast table three times in your join. For example:


select job.JobDesc, Gen.ContrName,Elec.ContrName,Mech.ContraName
from
 ContractorMast Gen,
 ContractorMast Elec,
 ContractorMast Mech,
 JobMast job
where
job.GenContractorID = Gen.ContrID and
job.ElecContractorID = Elec.ContrID and
job.MechContractorID = Mech.ContrID
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date