mssql - query with results from same table and association table

Question

I need help with a query.

I have 3 tables:

1. "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

2. "Equipment" table

Id	Name	EquipmentNumber
1	Heavy Eqp	EQL335
2	Light Eqp	EQL873

3. "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:

1. we do not query for CTypeId 1010, so no need to consider that case
   
2. 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.

Answer 1

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'

Result :

EquipmentNumber	PartNumber	ChildPartNumber	CTypeId	VendorId
EQL335	TR001	GR01-4324	441	V1
EQL335	CS001	GR01-378	441	V1
EQL873	CS001	GR01-378	441	V1

db<>fiddle demo