Unanswered: Match a full column to a part of another
I'm having issues making a join of two tables.
Route varchar (20)
Item char(2) (just 1 item per record i.e. "XX")
Items varchar(128) (many items per record i.e. "XY, XX, YY")
I need to make a report that shows the name from table1 and the benefits from table2, where they have the same route on both tables AND also -here's the complexity and the question- where the Item from table1 IS CONTAINED in table2. So I thought of
SELECT table1.Name, table2.Benefits
FROM table1 INNER JOIN table2
ON (table1.Route = table2.Route
AND table2.Items LIKE '%' + table1.Item + '%' )
But the "LIKE" comparison with wildcards does not work when comparing two columns. It only works when comparing a column with a string or variable.
charindex(Table1.Item,Table2.Items) > 0 doesn't work either, and I don't know why.