Select the same field based on different foreign keys

Question

I have two tables:

1. Containing a list of products with ids and names
2. A table linking several products with parent-child relationship based on ids using foreign keys.

I want to create a view that will display the product name for both parent and child but both derives from the same table.

1. Each product can be a parent for multiple children
2. Each product can be a child for multiple parents
3. The parenting relationship goes only one level without the need for recursion (no grandchildren)

Here is a sample of the construction of tables:

create table plugins_t
(
  rid          int auto_increment,
  product_name varchar(156) not null,
  constraint plugins_t_rid_uindex
    unique (rid)
);

alter table plugins_t
  add primary key (rid);

create table parent_2_childs_t
(
  rid              int auto_increment,
  parent_plugin_id int not null,
  child_plugin_id  int not null,
  constraint parent_2_childs_t_rid_uindex
    unique (rid),
  constraint parent_2_childs_t_plugins_t_rid_fk
    foreign key (parent_plugin_id) references plugins_t (rid),
  constraint parent_2_childs_t_plugins_t_rid_fk_2
    foreign key (child_plugin_id) references plugins_t (rid)
);

alter table parent_2_childs_t
  add primary key (rid);

INSERT INTO plugins_t
  (product_name)
values
  ('product1'),
  ('product2'),
  ('product3'),
  ('product4');

INSERT INTO parent_2_childs_t
  (parent_plugin_id, child_plugin_id)
VALUES
  (1, 3),
  (2, 3),
  (2, 4);

INSERT INTO plugins_t
  (product_name)
values
  ('product1'),
  ('product2'),
  ('product3'),
  ('product4');

INSERT INTO parent_2_childs_t
  (parent_plugin_id, child_plugin_id)
VALUES
  (1, 3),
  (2, 3),
  (2, 4);

I used a two select using a subquery but I get duplicate values.

select
       pt.product_name as parent_product_name,
       c.product_name as child_product_name
from plugins_t as pt
right join parent_2_childs_t p2ct on pt.rid = p2ct.parent_plugin_id

  join (
    select pt2.product_name from plugins_t as pt2
    join parent_2_childs_t p2ct2 on pt2.rid = p2ct2.child_plugin_id
    ) as c

This is the desired output:

| Parent name  | child name  |
------------------------------
| product1     | product3    |
| product2     | product3    |
| product2     | product4    |

Answer 1

The query worked

  select parent.product_name as parent_product_name, child.product_name as child_product_name
  from plugins_t as parent inner join parent_2_childs_t p2ct on parent.rid = p2ct.parent_plugin_id
  inner join plugins_t as child on child.rid = p2ct.child_plugin_id