Inner-join in sql with contains condition

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.

Solution

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.