SQL Server 2019

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


(No column name)


Microsoft SQL Server 2019 (RTM-CU3) (KB4538853) - 15.0.4023.6 (X64) 
	Mar  4 2020 00:59:26 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table parent(material int,Attachment int);
create table child(parent int,child int);

insert into parent values(101,1);
insert into parent values(102,2);
insert into parent values(201,4);
insert into parent values(202,4);
insert into parent values(301,2);

insert into child values(101,201);
insert into child values(101,202);
insert into child values(101,204);
insert into child values(101,205);
insert into child values(102,301);
insert into child values(102,302);
11 rows affected

select * from parent


material
Attachment


101
1

102
2

201
4

202
4

301
2


select * from child


parent
child


101
201

101
202

101
204

101
205

102
301

102
302


select c.parent,c.child,Attachment from parent p
join child c
on p.material=c.[child]


parent
child
Attachment


101
201
4

101
202
4

102
301
2


select c.parent,c.child,Attachment from parent p
join child c
on p.material=c.parent


parent
child
Attachment


101
201
1

101
202
1

101
204
1

101
205
1

102
301
2

102
302
2