This question was asked to me in an interview. I have two tables as given below

Table1 : [table1]

Table2 : [table2]

I want the following table as my output. Please tell me the SQL query which can be used to join the two tables and get the output as given below.

Expected Output : [output]

Sorry for adding the images as I could not figure out how to add the tables

CodePudding user response：

One solution that should work in the majority of databases just uses like to match on the strings.

Also note the sample data is incorrect for B - Orange does not appear in the results.

select t1.name, t2.fruit, t2.value
from t1 join t2 on t1.fruits like Concat('%',t2.fruit,'%')