Combining Three Tables

[Millsinc]

Good Night I am seeking some help with a sql question. The question is: For both Electrical and Mechanical components list the total number under each category and the total number of suppliers that supply those components under each category. Note# A component can have a maximum of three levels of subcomponents. I tried using a nested IF statement and also and Inner Join but neither of them is working...I am a bit lost and confused and how to go next. Codes I tried:SELECT SupID, (SELECT COUNT(e.PartNo) AS myTotalElec, COUNT(s.SupID) AS myTotalElecSupplierFROM ElecComponent AS e, Supplier AS sWHERE s.PartNo=e.PartNo), (SELECT COUNT(m.PartNo) AS myTotalMech, COUNT(s.SupID) AS myTotalMechSupplierFROM MechComponent AS m, Supplier AS sWHERE s.PartNo=m.PartNo)FROM Supplier AS sORDER BY SupID; SELECT Supplier.SupName, ElecComponent.PartNo, MechComponent.PartNoFROM ElecComponent, MechComponentINNER JOIN SupplierON Supplier.SupID=ElecComponent.PartNoORDER BY SupName; Could someone assist me? RegardsMills

[justsomeguy]

Are you expected to solve that with just a single SQL query? Are you allowed to use other languages?

[Millsinc]

Hi I must use SQL only and have to solve with a single query..

[justsomeguy]

If I'm reading the question correctly, the 3 levels of subcategories is the sticking point for me. It sounds like you may need to join on the same tables several times, and each time you're looking for another level in the category tree. What's the structure of the tables you have?