you appear to want to match "corn" with "cornil"...is this correct?

some set up data to play with,,,,,

CREATE TABLE [dbo].[Table1] (

[id] [bigint] NULL,

[product_name] [nvarchar](1000) NULL,

[quantity] [int] NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[Table2] (

[id] [bigint] NULL,

[product_name] [nvarchar](1000) NULL,

[details] [nvarchar](1000) NULL,

[description] [nvarchar](1000) NULL

) ON [PRIMARY]

INSERT INTO [Table1]([id],[product_name],[quantity])

VALUES(1,'canola',120)

INSERT INTO [Table1]([id],[product_name],[quantity])

VALUES(2,'bread',130)

INSERT INTO [Table1]([id],[product_name],[quantity])

VALUES(3,'sauce',140)

INSERT INTO [Table1]([id],[product_name],[quantity])

VALUES(4,'corn',120)

INSERT INTO [Table2]([id],[product_name],[details],[description])

VALUES(1,'canola,tea,muffin,cheese ','jellyproducts',NULL)

INSERT INTO [Table2]([id],[product_name],[details],[description])

VALUES(2,'vinegar,canola,sunflower ','oilproducts',NULL)

INSERT INTO [Table2]([id],[product_name],[details],[description])

VALUES(3,'cornil,vegoil,canola,sesameoil ','oilproducts',NULL)

GO

/*Try1 : */

SELECT t2.id,

t2.product_name,

t2.details,

t1.id AS T1_row

FROM Table1 AS t1

INNER JOIN Table2 AS t2 ON t2.product_name LIKE '%' + t1.product_name + '%'

/*Try2: */

SELECT T2.id,

T2.product_name,

T2.details,

T1.id AS T1_row

FROM Table2 AS T2

INNER JOIN Table1 AS T1 ON CHARINDEX(T1.product_name, T2.product_name) > 0