Get attribute of Child from another table

Question

I have prepared a FIDDLE HERE

I have a parent table which looks like below.

+----------+------------+
| material | Attachment |
+----------+------------+
| 101      | 1          |
| 102      | 2          |
| 201      | 4          |
| 202      | 4          |
| 301      | 2          |
+----------+------------+

Also the relation table as below

+--------+-------+
| parent | child |
+--------+-------+
| 101    | 201   |
| 101    | 202   |
| 101    | 204   |
| 101    | 205   |
| 102    | 301   |
| 102    | 302   |
+--------+-------+

Im trying to get the attachment of the child in the below format. Expected output.

+--------+-------+------------+
| parent | child | Attachment |
+--------+-------+------------+
| 101    | 201   | 4          |
| 101    | 202   | 4          |
| 101    | 204   | Child NA   |
| 101    | 205   | Child NA   |
| 102    | 301   | 2          |
| 102    | 302   | Child NA   |
+--------+-------+------------+

I have tried this query. But i'm getting the attachment of Parent instead of child.

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

Getting below.

+--------+-------+------------+
| parent | child | Attachment |
+--------+-------+------------+
| 101    | 201   | 1          |
| 101    | 202   | 1          |
| 101    | 204   | 1          |
| 101    | 205   | 1          |
| 102    | 301   | 2          |
| 102    | 302   | 2          |
+--------+-------+------------+

Answer 1

I think this is a left join:

select r.parent, r.child, p.attachment
from relation r left join
     parent p
     on p.material = r.child;

This produces NULL rather than 'Child NA'.

Here is a db<>fiddle.