2

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    |
3
  • I guess you're missing a join condition.
    – mustaccio
    Dec 12, 2018 at 15:44
  • Welcome to dba.se! Bravo on the effort for your first post! Could you add a tag for your dbms (looks like 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? Dec 12, 2018 at 15:44
  • Thank you for your input! I have added the mysql tag (as you guessed). I edited the question so it will be clear that I'm not looking for recursion looking up grandchildren - only a simple join. The main problem is that I want to select the same field (product_name) twice - each time based on a different foreign key. p.s. This is my first time posting here, but I have quite a milage in stackoverflow. Thanks for the feedback!
    – Nir
    Dec 13, 2018 at 6:34

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.