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 ContrIDJobDescription -----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:
The closest I’ve gotten is using INNER JOIN to get the result as separate records:
JobDesc|GenContractor|ElecContractor|MechContractr
JobDesc|GeneralContractorJobDesc|ElecContractorJobDesc|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.ContraNamefrom ContractorMast Gen, ContractorMast Elec, ContractorMast Mech, JobMast jobwherejob.GenContractorID = Gen.ContrID andjob.ElecContractorID = Elec.ContrID andjob.MechContractorID = Mech.ContrID