Link Multiple values in Single Column

Question

Good Day

I have a table with all my data. In Column Products there are values linked to an index table but instead of it having a single value it has multiple values i.e. 900;190;170. If it was a single value I would have just use a Left Join between my two tables on Column Products. Is there a way to link it against the multiple values? 900 = Other, 190 = Books and 170 = Pens. The other problem is that it can be any amount of values in the Product column.

Thanks Ruan

Answer 1

-- create a table
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name varchar(20)  NOT NULL
);
CREATE TABLE productsIndex (  
  indexName varchar(120)  NOT NULL,
  itemIndex varchar(20)  NOT NULL
);

-- insert some values
INSERT INTO products VALUES (900, 'Books');
INSERT INTO products VALUES (800, 'Other');
INSERT INTO products VALUES (190, 'Pen');

-- insert some values
INSERT INTO productsIndex VALUES ('900;190;170', 'test');
INSERT INTO productsIndex VALUES ('800;100', 'test2');

SELECT p.* ,pi.itemIndex
    FROM products p
    join productsIndex pi
        on p.id  in (SELECT value FROM STRING_SPLIT(pi.indexName, ';'))