How do I write a SQL 2k query to achieve the following records?

I Currently have 3 tables populated with the following :


1  Contract1a    Contract1b    Contract1c    Customer1a
2  Contract2a    Contract2b    Contract2c    Customer2a


1  Customer1a    Customer1b    Customer1c
2  Customer2a    Customer2b    Customer2c


1  Equip1a     Equip1b      Equip1c      Contract1a
2  Equip2a      Equip2b      Equip2c      Contract1a
3  Equip3a      Equip3b      Equip3c      Contract1a
4  Equip1a      Equip1b      Equip1c      Contract2a
5  Equip2a      Equip2b      Equip2c      Contract2a


I want my query to return (each equipment record to be populated with all its respective contract and some customer data. Admittedly a lot of duplication but an application that I've lost the source code to modify, requires this structure to work):

1  Contract1a      Contract1b      Contract1c      Customer1c      Equip1a    Equip1b    Equip1c
2  Contract1a      Contract1b      Contract1c      Customer1c      Equip2a    Equip2b    Equip2c
3  Contract1a      Contract1b      Contract1c      Customer1c      Equip3a    Equip3b    Equip3c
4  Contract2a      Contract2b      Contract2c      Customer2c      Equip1a    Equip1b    Equip1c
5  Contract2a      Contract2b      Contract2c      Customer2c      Equip2a    Equip2b    Equip2c       

What is the best way of achieving the above? Inside the following Loop is the coding I have, but doesn't return any of the equipment.


  Select Ctract.FirstContractCol, Ctract.SecondContractCol, Ctract.ThirdContractCol, Cstmr.ThirdCustomerCol -- I want to include: FirstEquipCol, SecondEquipCol, ThirdEquipCol
  From ContractTable As Ctract Inner Join
       CustomerTable As Cstmr On Ctract.ThirdContractCol = Cstmr.FirstCustomerCol

From EquipTable
Where ForthColName = [Each Contract]
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Select Co.ContractA, Co.ContractB, Co.ContractC, CU.CustomerA, Cu.CustomerB, Cu.CustomerC, EQ.EquipmentA, EQ.EquipmentB, EQ.EquipmentC
From EquipTable EQ
inner join ContractTable CO on EQ.ContractA = CO.ContractA
inner join CustomerTable CU on CO.CustomerA = CU.CustomerA

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Try  this, but provide the correct name for CustomerColumn:
FROM EquipTable as e, ContractTable As c
WHERE e.CustomerColumn=c.CustomerColumn

Open in new window

IvanHowarthAuthor Commented:
I've followed dgmg's solution simply because his was first and worked, nevertheless I thank you both.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.