I have two tables:
- Containing a list of products with ids and names
- 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.
- Each product can be a parent for multiple children
- Each product can be a child for multiple parents
- 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 |
mysql
, but didn't want to assume)? As well - given your sample data and desired output, I'm not sure if you mean to ask about recursion or a simple join set for your final output. Could you clarify if you want to enumerate grandchildren, great-grandchildren etc? Or is this just about removing the duplicate values?