"Like" Operator in Inner Join in SQL

Like operator in inner join in SQL

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.

Use a LIKE clause in part of an INNER JOIN

Your first query will work but will require a full table scan because any index on that column will be ignored. You will also have to do some dynamic SQL to generate all your LIKE clauses.

Try a full text search if your using SQL Server or check out one of the Lucene implementations. Joel talked about his success with it recently.

Inner join with like operator?

MS-Access uses * where you have %

https://support.office.com/en-gb/article/like-operator-b2f7ef03-9085-4ffb-9829-eef18358e931

(One of many reasons I hate MS-Access, it's too proprietary)

Like operator in SQL Inner Join

I think you are compare # too... try to replace it:

Select 
      t1.Handle , 
      t1.Name , 
      t1.Description 
from 
      table1 t1 inner join table2 t2 
      on t2.Handle = t1.Handle
      inner join table3 t3 
      on t2.Handle like CONCAT('%',REPLACE( t3.CurrentHandle,'#',''), '%')

Hope this help!

Inner join with like clause

you have to form the clause using concat ...

...LIKE CONCAT('%',tbl_albums.name, '%');

there is no + operator like this in mysql

---

Related Topics