Join MySQL tables using LIKE?

I have a requirement to join to table where the values in the match columns are a subset of one another as per example below.

Table 1 (T1) has key values of format:
apple
banana
orange

Table 2 (T2) has values of format:
apple 123
yellow banana
123456 orange
the orange tree

What I want is a query that matches the T1 value with corresponding substring in T2 giving the following result:
apple, apple 123
banana, yellow banana
orange, 123456 orange
orange, the orange tree

I'm trying to join using LIKE but haven't figured out the syntax. Is there an easy way to do this.

 

[COLOR="Blue"]select [/COLOR]
	t1.id, [COLOR="Green"]-- assming this is the id field holding the apple text[/COLOR]
	t2.[text] [COLOR="Green"]-- assuming this is the fieldname holding the apple 123 text..[/COLOR]
[COLOR="Blue"]from [/COLOR]
	t1 [COLOR="SlateGray"]inner join [/COLOR]t2 [COLOR="Blue"]on [/COLOR]
	t2.text [COLOR="SlateGray"]like [/COLOR][COLOR="Red"]'%'[/COLOR] + t1.id + [COLOR="Red"]'%'[/COLOR]

Code (markup):

note that this kind of join is not very efficient .. (get worse the more the data are..)

take care

 

Thanks, I ran a test through phpMyadmin and got the following error. Any ideas what is wrong?

SELECT t1.id, t2.text
FROM t1
INNER JOIN t2 ON t2.text LIKE '%' + t1.id + '%'
LIMIT 0 , 30

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+ t1 . id + '%'
LIMIT 0, 30' at line 1

 

hmm..

try changing the

[COLOR="SlateGray"]like [/COLOR][COLOR="Red"]'%'[/COLOR] + t1.id + [COLOR="Red"]'%'[/COLOR]

Code (markup):

to

[COLOR="SlateGray"]like [/COLOR]concat([COLOR="Red"]'%'[/COLOR] , t1.id , [COLOR="Red"]'%'[/COLOR])

Code (markup):

also make sure you alter the query to match the names of your fields...

 

perfect ! - thanks

id text
apple apple 123
banana yellow banana
orange 123456 orange
orange the orange tree