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

I Currently have 3 tables populated with the following :

ContractTable

1  Contract1a    Contract1b    Contract1c    Customer1a
2  Contract2a    Contract2b    Contract2c    Customer2a


CustomerTable

1  Customer1a    Customer1b    Customer1c
2  Customer2a    Customer2b    Customer2c


EquipTable

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.


SomeKindOfLoop

  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

EndLoop
From EquipTable
Where ForthColName = [Each Contract]
IvanHowarthAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
dqmqConnect With a Mentor Commented:

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

0
 
hieloCommented:
Try  this, but provide the correct name for CustomerColumn:
SELECT e.*
FROM EquipTable as e, ContractTable As c
WHERE e.CustomerColumn=c.CustomerColumn

Open in new window

0
 
IvanHowarthAuthor Commented:
I've followed dgmg's solution simply because his was first and worked, nevertheless I thank you both.
0
All Courses

From novice to tech pro — start learning today.