Join tables based on variable value contained in column

join;like

I have two tables with dates as the primary column to join. But, I also need to join based on the value from one column that is partially contained in a column from the other table. 

Table 1: date, audience

Table 2: date, channel

'audience' in table 1 has a value that is partially contained in 'channel' in table 2 like this: 

audience = "oldpeople"

channel = "oldpeople_email"

There are hundreds of potential 'audience' values and 'channel' values, so I cannot name the values, it has to work dynamically. This is what I have tried that has not worked:

SELECT * FROM table1 
left JOIN table2 ON ((table2.Channel like '%table1.audience%')
     AND (table2.date = table1.date) )

Best Answer

With the way you've written that join condition, SQL server will search for exactly "table1.audience", not the column value. Column names within single quotes are just treated as string literals, not the underlying column value.

Try this instead:

SELECT * 
FROM table1 
LEFT JOIN table2 
ON ((table2.Channel like '%' + table1.audience + '%') 
AND (table2.date = table1.date))

This will concatenate the value contained within table1.audience and wrap it in wildcards.

I'd recommend dropping the leading wildcard if it's not necessary, as this will significantly hinder performance if you're dealing with a large data set (as you will force a scan).

Related Question