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
– Roy, Commented Jan 24, 2017 at 14:03
Why it should retun a result? OP1,BP1,33:A isn't LIKE '%OP1,BP1,33:#%' Or am I wrong? — Marcus
– Marcus, Commented Jan 24, 2017 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
– Roy, Commented Jan 24, 2017 at 14:07
If not normalize then add/use a surrogate key to avoid certain complexities of this nature. — xQbert
– xQbert, Commented Jan 24, 2017 at 14:10

M84 · Accepted Answer · 2017-01-24 14:08:17Z

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!

Thank you so much for the reply.. Yes i was missing the replace function in my query.

Matt · Accepted Answer · 2017-01-24 14:08:58Z

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.

Marcus · Accepted Answer · 2017-01-24 14:13:16Z

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)

Collectives™ on Stack Overflow

Like operator in SQL Inner Join

3 Answers 3

1 Comment

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Linked

Related