[RESOLVED] Using Relative Comparisons on Join?

rulian

Is it possible to do a LIKE comparison on Join?

Such as

Select * from TABLE1 AS T1
LEFT JOIN TABLE 1 AS T2 WHERE T2.ID LIKE %T1.LINK%
WHERE T1.id = 2

cgraz

Have you tried it?

What is it you're trying to do anyways?

tomhath

Use ON for the condition in a JOIN; the condition can be any boolean expression.

Every pair of rows in the two tables are evaluated against the expression; when it returns "true" that pair of rows is part of the result set (of course with a LEFT JOIN, each row in the left table will either be true with one or more rows in the other table, or will be in the result set with nulls for the columns from the second table).

So the answer to your question is yes.

Select * 
from TABLE1 AS T1
LEFT JOIN TABLE1 AS T2 
ON T2.NAME LIKE '%' + T1.NAME + '%'
WHERE T1.id = 2

rulian

thank you tom

I tried your solution, however it keeps giving me sql errors
it seems it doesnt like the '%' + T1.Name + '%' syntax, its the combo that I seem to be getting stuck on.

rulian

ahh it's
Select *
from TABLE1 AS T1
LEFT JOIN TABLE1 AS T2
ON T2.NAME LIKE conCAT('%' , T1.NAME , '%')
WHERE T1.id = 2

tomhath

Depends on the DBMS. What I posted should work on SQLServer. I don't use MySQL enough to remember all the nuances :o