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.

Allout

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.

Symbolism

@Rimas good eye. ',' + replace(table1.locations,', ', ',') + ',' like '%,' + table2.location + ',%' in the join might look ugly but may do the trick – Symbolism

@AnoopJoshi You are quite welcome. Hope you can tweak this query to your needs – Symbolism

Answer 2

If you are using Mysql you could take a look at the following option: INSTR

select table2id, location, table1.id, locations
from table2 inner join table1 on instr(locations,location) >= 1; 

SQL Fiddle Link

Edwardedwardian

Answer 3

I'm new to the SQL world and I faced this exact problem. Much bigger than this actually but this was the last piece of the puzzle to get what I wanted.

Here's how I solved it:

SELECT * FROM t1 CROSS JOIN t2
WHERE t1.Locations CONTAINS t2.Location;

Use the CONTAINS according to the language we're using.

To think how close I came to giving up on the puzzle and how simple the solution really was.

Stutzman