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) )
  • What RDBMS are you using ?
    – a1ex07
    Mar 8 '18 at 2:40

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:

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).

  • Fantastic- that worked. Thanks for the great and quick explanation!
    – Steve L
    Mar 8 '18 at 2:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.