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
Id | Name | PartNumber | CTypeId | MainPartId | VendorId |
---|---|---|---|---|---|
1 | transmission 01 | TR001 | 1010 | 0 | V1 |
2 | gear 4324 | GR01-4324 | 441 | 1 | V1 |
3 | gear 223 | GR11-223 | 442 | 1 | V1 |
4 | gasket 56 | GS7-56 | 10 | 1 | V1 |
5 | motor 01 | CS001 | 1010 | 0 | V1 |
6 | gear 378 | GR01-378 | 441 | 5 | V1 |
explanation:
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
Id | Name | EquipmentNumber |
---|---|---|
1 | Heavy Eqp | EQL335 |
2 | Light Eqp | EQL873 |
- "EquipmentPartAssoc" table
This is a table which contains the association for equipment-parts
Id | EquipmentId | PartId |
---|---|---|
1 | 1 | 1 |
2 | 1 | 5 |
3 | 2 | 5 |
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)
EquipmentNumber | PartNumber | ChildPartNumber | CTypeId | VendorId |
---|---|---|---|---|
EQL335 | TR001 | GR01-4324 | 441 | V1 |
EQL335 | CS001 | GR01-378 | 441 | V1 |
EQL873 | CS001 | GR01-378 | 441 | V1 |
additional notes:
- 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.