Query wildcarding a joined table column?

gregolson

Hi all,

I've got 2 tables (say t1, and t2), and this works fine and as expected:

SELECT * from t1, t2 WHERE t1.columnX = t1.columnY

However, I would like to do the following:

SELECT * from t1, t2 WHERE t1.columnX LIKE '%t1.columnY%'

I believe as written, t1.columnY is used as a string, and thus not what I want (want wildcards prepended/appended to column value).

Is there a way to make this wildcard search work?

Many thanks in advance.

Cheers,

--Greg

paulnaj

Hi Greg,

Do you mean ...

SELECT * from t1, t2 WHERE t1.columnX LIKE '%' + t1.columnY +' %'

Paul 🙂

gregolson

Hi Paul,

Thanks a bunch for the reply. Looks like I made a typeo in my original post. Here's what I really meant (me and my fat fingers):

SELECT * from t1, t2 WHERE t1.columnX LIKE '%t2.columnY%'

Basically, I want to match when t2.columnY is contained by some manner in t1.columnX.

I'm by no means a sql expert. From what I gather, this would be fairly easy using a sub-select, but I'm stuck with a hosted MySQL which is less than version 4.1, so sub-select's are not yet there.

Any help or guidance appreciated.

Thanks again for the reply.

Cheers,

--Greg

paulnaj

Well, ...

SELECT * from t1, t2 WHERE t1.columnX LIKE '%' + t2.columnY +' %'

Paul.

gregolson

Hey Paul,

Thanks for the reply. Can you explain what the '+' operator does on the right side of that LIKE clause? I'm not familiar with this. I tried the following based on your suggestion:

SELECT DISTINCT t1.*
FROM pmd_users AS t1, tmp
WHERE t1.category LIKE 'XX' + tmp.FOO;

Surprisingly, this returned every row in table t1 (no category in t1 has an 'XX' in it).

Thanks in advance for the explanation.

Cheers,

--Greg

paulnaj

Oops,

It was supposed to mean concatenation, but I'd 'morphed' into another language.

Should use the CONCAT() function ...

SELECT * from t1, t2 WHERE t1.columnX LIKE CONCAT('%', t2.columnY, '%')

Paul 🙂

gregolson

Hi Paul,

That worked great! Thanks a bunch for the help; I really appreciate it!

Cheers,

--Greg