Inner-join in sql with contains condition

Question

I have 2 tables like this,

Table1

Id     Locations
--     ---------
1      India, Australia
2      US , UK 

Table2

Table2Id    Location
--------    --------
101         Italy
102         UK
103         Hungary
104         India

I need to inner join these 2 tables on the condition, If Locations in table2 contains Location field in table1. The result will be like

Id   Table2Id    Location     Locations
--   --------    --------     ---------
1     104        India        India, Australia
2     102        UK           US , UK 

I tried something like

Select t1.id,
       t2.Table2Id,
       t1.Locations,
       t2.Location
From Table1 t1 
Inner join Table2 t2 On CONTAINS(t1.Locations, t2.Location)

But the second parameter of contains should be a string. Its not allowing to give the column name there.

I cannot use temptable or variable in the query. Because this query needs to be run on a email campaign tool called ExactTarget where there is no support for temptable and variables.

Any help will be highly appreciated. Thank you.

Answer 1

SQLFiddle example for MySQL 5.5 SQLFiddle example for SQL

Table and data

create table table1 (id int, locations varchar(100));
insert into table1 values 
(1, 'India, Australia'),
(2, 'US, UK');

create table table2 (table2id int, location varchar(100));
insert into table2 values
(101, 'Italy'),
(102, 'UK'),
(103, 'Hungary'),
(104, 'India');

MySQL query

select
  table1.id,
  table2.table2id,
  table2.location,
  table1.locations
from table1
join table2 on table1.locations like concat('%', table2.location, '%')

SQL Server query

select
  table1.id,
  table2.table2id,
  table2.location,
  table1.locations
from table1
join table2 on table1.locations like '%' + table2.location + '%'

Edit

In case where US location is contained in the country name Australia, the above query may not work as desired. To work around that problem, here's a possible query to use

select
  table1.id,
  table2.table2id,
  table2.location,
  table1.locations
from table1
join table2 on 
  ',' + replace(table1.locations,', ', ',') + ',' like '%,' + table2.location + ',%'

This query forces India, Australia to become ,India,Australia,. This is then compared with ,US, and therefore will not suffer from incorrect results.