SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Parts
(
    Id int,
    Name varchar(20),
    PartNumber varchar(10),
    CTypeId int,
    MainPartId int,
    VendorId varchar(2)
);

insert into Parts values
(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');
6 rows affected

create table Equipment
(
    Id int,
    Name varchar(20),
    EquipmentNumber varchar(10)
);

insert into Equipment values
(1,	'Heavy Eqp',	'EQL335'),
(2,	'Light Eqp',	'EQL873');
2 rows affected

create table EquipmentPartAssoc
(
    Id int,
    EquipmentId int,
    PartId int
);

insert into EquipmentPartAssoc values
(1,	1,	1),
(2,	1,	5),
(3,	2,	5);
3 rows affected

select e.EquipmentNumber,
       p.PartNumber,
       ChildPartNumber = c.PartNumber,
       c.CTypeId,
       c.VendorId
from   Parts c
       inner join Parts p on c.MainPartId = p.Id
       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'


EquipmentNumber
PartNumber
ChildPartNumber
CTypeId
VendorId


EQL335
TR001
GR01-4324
441
V1

EQL335
CS001
GR01-378
441
V1

EQL873
CS001
GR01-378
441
V1

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