Jump to content

Combining Three Tables


Millsinc
 Share

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...