All we need is an easy explanation of the problem, so here it is.
I need help with a query.
I have 3 tables:
- "Parts" table.
one important thing to note here is that, the parts table contains both main part and child part. Child parts are components of main part. The child part is linked to main part using a column "MainPartId"
here is the table
a) "transmission 01" (with Id value of 1), and "motor 01" (with Id value of 5) are main parts. main part always has "mainPartId" value as 0. And CTypeId as 1010
b) 2,3,4 rows are child parts of "transmission 01" , hence MainPartId is "1" (Id of "transmission 01"). Similarly 6 is a child part of "motor 01"
c) CType Id does NOT repeat for a given main part. ie: from the above table, you will see "gear 4324" has CTypeId od "441". this will not repeat for any child part of "transmission 01". But another main part -like "motor 01"- can have child part with CtypeId "441" as shown in the table
- "Equipment" table
- "EquipmentPartAssoc" table
This is a table which contains the association for equipment-parts
About the query and expected result:
The result I want is to list all mainpart – childpart combo with specific CTypeId for each equipment.
eg (if I want results for CtypeId 441 and vendor v1)
- we do not query for CTypeId 1010, so no need to consider that case
- In this case, only 1 vendor "V1" is given in the table. But I hope you get the point that there can be many vendors.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Using inner join, starts from the
Parts table and self join to get the Parent’s information.
select e.EquipmentNumber, p.PartNumber, ChildPartNumber = c.PartNumber, c.CTypeId, c.VendorId from Parts c -- child inner join Parts p on c.MainPartId = p.Id -- parent inner join EquipmentPartAssoc a on p.Id = a.PartId inner join Equipment e on e.Id = a.EquipmentId where c.CTypeId = 441 and c.VendorId = 'V1'
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂