How to append column value from one table to another table based on the primary key-CodePudding

CREATE TABLE tab_risk (
    prod_id      NUMBER(10),
    prod_name    VARCHAR2(30),
    prod_filter  VARCHAR2(30),
    constraint pk_tab_risk primary key (prod_id)
);

insert into tab_risk values(1,'a','Falcon');
insert into tab_risk values(2,'b','Cars');
insert into tab_risk values(3,'c','Powerpoint');
insert into tab_risk values(4,'d','Zone');

CREATE TABLE ref_filter (
    add_in_filter VARCHAR2(30)
);

insert into ref_filter values('Powerpoint');
insert into ref_filter values('Word');
insert into ref_filter values('Email');

I need to append the ref_filter data to a particular prod_id. But I am wondering how this can be done or is this possible or not? Like for prod_id 1 currently, prod_filter is 'Falcon' but when I select

prod_id 1 it should give me 'Falcon', 'Powerpoint', 'Word', and 'Email'. And likewise for prod_id 2 and so on. Is there any way to achieve this?

CodePudding user response：

One option is to create a view and select from it (instead of from the table):

SQL> create or replace view v_filter as
  2  select r.prod_id, r.prod_filter
  3  from tab_risk r
  4  union
  5  select r.prod_id, f.add_in_filter
  6  from tab_risk r cross join ref_filter f;

View created.

SQL> select *
  2  from v_filter
  3  where prod_id = 1;

   PROD_ID PROD_FILTER
---------- ------------------------------
         1 Email
         1 Falcon
         1 Powerpoint
         1 Word

SQL>

The view can then be joined to tab_risk table to select other columns (if you need them).

[EDIT: this is query you posted as a comment, fixed]

SQL> SELECT r.prod_id, r.prod_name, r.prod_filter
  2    FROM tab_risk r
  3  UNION
  4  SELECT r.prod_id, r.prod_name, f.add_in_filter
  5    FROM tab_risk r CROSS JOIN ref_filter f;

   PROD_ID PROD_NAME                      PROD_FILTER
---------- ------------------------------ ------------------------------
         1 a                              Email
         1 a                              Falcon
         1 a                              Powerpoint
         1 a                              Word
         2 b                              Cars
         2 b                              Email
         2 b                              Powerpoint
         2 b                              Word
         3 c                              Email
         3 c                              Powerpoint
         3 c                              Word
         4 d                              Email
         4 d                              Powerpoint
         4 d                              Word
         4 d                              Zone

15 rows selected.

SQL>