devxlogo

SQL Query

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                                  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:
JobDesc|GenContractor|ElecContractor|MechContractr
The closest I’ve gotten is using INNER JOIN to get the result as separate records:

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

devx-admin

Share the Post: