CODEWITHSUNDEEP
sqljoinsql-like
japem
japem

Reputation: 1111

"Like" operator in inner join in SQL

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.

Upvotes: 24

Views: 127119

Answers (5)

Mohammad
Mohammad

Reputation: 678

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

Upvotes: 0

Hassan Shahzad Aheer
Hassan Shahzad Aheer

Reputation: 75

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

Upvotes: 0

Reza
Reza

Reputation: 19843

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.

Upvotes: 4

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

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.

Upvotes: 63

Tomas Pastircak
Tomas Pastircak

Reputation: 2857

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.

Upvotes: 5

Related Questions