MKN MKN - 1 year ago 42
SQL Question

Join two values to get the description in other table-SQL

I have a scenario where I need to join two values and find the material desc.

Product table :
-------------
PRODUCT SUGGESTIONS
item1 item2
item1 item3
item1 item3&item4
item1 item2&item3

Product Master table:
--------------------

PRODUCT DESC
item1 EBIKE
item2 BIKE TOOLS
item3 BIKE PAINT
item4 Regular Bike


The output should

PRODUCT SUGGESTIONS PRODUCT DESC
item1 item2 BIKE TOOLS
item1 item3 BIKE PAINT
item1 item3&item4 BIKE PAINT& Regular Bike
item1 item2&item3 BIKE TOOLS&BIKE PAINT


I am not sure how to join these two tables. Kindly suggest.

MT0 MT0
Answer Source

If you are using substring matches (i.e. using INSTR() or LIKE) then there is the issue of making partial matches (i.e. item1 is a sub-string of item11) and you need to handle this case.

Assuming the delimiter you are using is never going to be part of the product id then you can use:

SELECT PT.PRODUCT,
       PT.SUGGESTIONS,
       LISTAGG(PM.DESC, '&') WITHIN GROUP (ORDER BY PM.DESC)
         AS PRODUCT_DESC 
FROM   PRODUCT PT
       INNER JOIN
       PRODUCTMASTER PM 
       ON ( '&'||PT.SUGGESTIONS||'&' LIKE '%&'||PM.PRODUCT || '&%' )
GROUP BY PT.PRODUCT, PT.SUGGESTIONS

However a better solution would be to properly separate the product and suggestions into individual tables:

CREATE TABLE products (
  id      NUMBER(8,0)  CONSTRAINT product__id__pk PRIMARY KEY,
  product VARCHAR2(20) CONSTRAINT product__product__u UNIQUE
);

CREATE TABLE suggestions (
  id          NUMBER(8,0) CONSTRAINT product_suggestions__id__pk PRIMARY KEY,
  product_id  NUMBER(8,0) CONSTRAINT product_suggestions__pid__fk
                                     REFERENCES products ( id )
                          CONSTRAINT product_suggestions__pid__nn NOT NULL
);

CREATE TABLE product_suggestions (
  suggestion_id NUMBER(8,0) CONSTRAINT product_suggestions__sid__fk
                                       REFERENCES suggestions ( id ),
  product_id    NUMBER(8,0) CONSTRAINT product_suggestions__sid__fk
                                       REFERENCES product ( id ),
  CONSTRAINT product_suggestions__pk PRIMARY KEY ( suggestion_id, product_id )
);