Like operator in SQL Inner Join

Question

I have three tables as:

Table1:

Handle       Name Description
OP1,BP1,33:A OP1  OP1 Desc

Table2:

Handle       Attribute Value
OP1,BP1,33:A Z_MARK    X

Table3:

Handle CurrentHandle ValuationPoint
Z:Y:Z  OP1,BP1,33:#  Disc Roll

Now i am trying to join the three tables so my query that i have come up with is:

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('%', t3.CurrentHandle, '%')

But as usual i am getting no records.

In the three tables table1 and table2 have same value for column Handle where as table2's Handle is almost similar to table3's CurrentHandle Column.

So i was trying to join these three tables with Like clause.

Thanks for the reply Sebas.It isn't returning any result Sebas. — Roy, Jan 24 '17 at 14:03
Why it should retun a result? `OP1,BP1,33:A` isn't `LIKE '%OP1,BP1,33:#%'` Or am I wrong? — Marcus, Jan 24 '17 at 14:05
Yes thats the point. OP1,BP1,33:A is almost similar to OP1,BP1,33:#. So i thought of implementing like clause — Roy, Jan 24 '17 at 14:07
If not normalize then add/use a surrogate key to avoid certain complexities of this nature. — xQbert, Jan 24 '17 at 14:10
My understanding is that '#' is a wildcard in like statements. But I might be wrong here — Sebas, Jan 24 '17 at 14:10
@sebas http://stackoverflow.com/questions/4300669/what-does-mean-in-sql — xQbert, Jan 24 '17 at 14:10
Allowed wildcards are `.` , `%` and `*` https://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html — Marcus, Jan 24 '17 at 14:11

score 1 · Accepted Answer · answered Jan 24 '17 at 14:08

1

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!

answered Jan 24 '17 at 14:08

M84

727
6
14

Thank you so much for the reply.. Yes i was missing the replace function in my query. – Roy Jan 24 '17 at 14:11

score 1 · Answer 2 · answered Jan 24 '17 at 14:08

As you have 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('%', t3.CurrentHandle, '%')

Returns nothing as OP1,BP1,33:A != %OP1,BP1,33:#%

This is assuming the # at the end of the t3.CurrentHandle is you describing a variable or unwanted character, then use:

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('%', SUBSTRING(t3.CurrentHandle, 1, CHAR_LENGTH(t3.CurrentHandle)-1)., '%')

This query works even if the last character isn't a`#`, it can be anything. — Matt, Jan 24 '17 at 14:45

score 0 · Answer 3 · answered Jan 24 '17 at 14:13

0

You could use SOUNDEX for fuzzy search/compare (https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_soundex)

Select t1.Handle , t1.Name , t1.Description from table1 t1
inner join table2 t2 on t2.Handle = t1.Handle
inner join table3 t3 on SOUNDEX(t2.Handle) = SOUNDX(t3.CurrentHandle)

answered Jan 24 '17 at 14:13

Marcus

1,910
2
16
27

1

Or even the `SOUNDS_LIKE()` function. – Matt Jan 24 '17 at 14:15

Like operator in SQL Inner Join

3 Answers3