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
See also  5 Ways to Improve Customer Experience
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist