"Like" operator in inner join in SQL

Question

Using Sequel Pro, I have these two tables:

Table1

Name         Year    x      y
John Smith   2010    10     12
Adam Jones   2010    8      13
John Smith   2011    7      15
Adam Jones   2011    9      14
etc.

and

Table2

Name                    Year  z    
Smith John Smith John   2010  27
Jones Adam Jones Adam   2010  25
Smith John Smith John   2011  29
Jones Adam Jones Adam   2011  21
etc.

Basically, the names in Table2 are the same only with the last name and first name switched, then repeated once. So the Names in Table1 are found in the names of Table2 ("John Smith" is found in "Smith John Smith John"). I want to perform an inner join and connect the z value of Table2 to the other values of Table1 and get something like this:

Name       x     y     z
John Smith 10    12    27
Adam Jones 8     13    25

So to do that, I ran this query:

Select Table1.*, Table2.z
From Table1
Inner join Table2
On Table1.Name like "%Table2.Name%" and Table1.Year=Table2.Year

But I got this as the output:

Name  Year  x  y  z

And that's it. I got the headings, but no rows. I don't know what I'm doing wrong... I suspect it probably has to do with the way I'm using the like operator but I don't know. Any help would be much appreciated.

Mentally

Answer 1

A bit of an odd data model aside, you've turned the tables around in the LIKE part (table1.name should be a part of table2.name, not the other way around), and you need to add the percents to the value, not the name of the field, that means not quoting the name;

SELECT table1.*, table2.z
FROM table1
INNER JOIN table2
  ON table2.name LIKE CONCAT('%', table1.name, '%') 
 AND table1.year = table2.year

An SQLfiddle to test with.

Epicanthus

TIL; CONCAT came with Sql Server 2012 and 2008 r2 doesn't have it. – Pennate

To expand on Ege's comment, I had to use the following to do this on an older version of SQL: ON table2.name LIKE ('%' + table1.name + '%') – Enswathe

@Joachim, How to improve performance using like statement in Join ? I have many records and it takes lot of time to run the query – Sidnee

Answer 2

Your query is incorrect, you are saying that the content of the column should be like abcdTable2.Nameefgh. This would be correct:

Select Table1.*, Table2.z
From Table1
Inner join Table2
On Table1.Name like "%" + Table2.Name+ "%" and Table1.Year=Table2.Year

This query will be quite slow for bigger table, but I'm afraid that if you are joining on a name only, the table can't really be bigger as you'll have duplicates quite soon.

Jem

Answer 3

try this:

Select Table1.*, Table2.z
From Table1
Inner join Table2
On Table1.Name like Concat('%',Table2.Name,'%') and Table1.Year=Table2.Year

in your query it will search for string containing Table2.Name (it is like constant)

as a suggestion joining on names is very very bad, what if you have 2 person with the same name??! So you need to have a primary and foreign key for this.

Boniface

Hmmm... this seems like it should work, but I get an error message saying that the syntax is wrong at the first plus sign. – Mentally

@user3081195 I have checked the query on SQL 2008 and it works fine. Are you using SQL Server? – Boniface

I am using Sequel Pro – Mentally

@user3081195 Sorry I didn't notice that – Boniface

Answer 4

maybe that would work for someone.

Select table1.*, table2.title,table2.password
From table1
Inner join table2
On table1.project_title like Concat('%', 'j' ,'%') WHERE table2.project_id = projects.id

Insincere

Answer 5

This can be achieved using REGEXP_CONTAINS() function as well, given below:

SELECT table1.*, table2.z
FROM table1
INNER JOIN table2  ON
table1.year = table2.year
where REGEXP_CONTAINS(table2.name,table1.name) = True

Agreement