MySQL: INNER JOIN and LIKE

[Veil]

Hey there. Hope someone can help me with this problem

I use a query like this:

SELECT a.foo, b.bar, c.bla
FROM `wee` a
INNER JOIN (`hoo` b, `haa` c)
ON (a.this = b.that AND a.this = c.that)

No problems here. But now I want to use "LIKE" between two values in the database itself. My idea was:

ON (a.this = b.that AND a.this LIKE '%c.that%')

But this doesn't seem to work, and I can't find out how I should format this. Does anyone have a idea?

[mcibor]

Hi Veil, and welcome to Webmasterworld!

I think that the problem is you used a table name, which you can't use in LIKE.
I am not sure how to do that. And to say the truth I don't know even why do you want to do that. The closest would be:

SELECT a.foo, b.bar, c.bla
FROM `wee` a
INNER JOIN (`hoo` b, `haa` c)
ON (a.this = b.that AND a.this = c.that)
WHERE c.that LIKE '%$search%';

I don't see any other option to do that, sorry

Michal

[riquez]

I've been trying to do something very similar, where I need to use LIKE <fieldname>

I got stuck with the complexity of my search/query & abandoned it - but I did find out how to get the LIKE <fieldname> part working.

ON (a.this = b.that AND a.this LIKE CONCAT('%', c.that, '%')

Hope it helps.

PS: In case anyone can help with my query, here it is...

SELECT * FROM products p, orders o
WHERE (p.cCat='WA' AND (p.cMuseum='C' OR p.cMuseum='N'))
OR (p.cMuseum='Y' AND o.cItems LIKE CONCAT('%', w.cModel, '%') AND o.pDate>'$date' AND o.cStatus='C')
ORDER BY p.cMake, p.cModel

Here's a quick explaination:
cCat is Category code
cMuseum is Yes, No, Coming, Xcluded
cItems is an text string including Product code, make, model etc = so i need to search it for Model (which is uniquely identifying)
pDate is date of order.
cStatus is Complete, Declined, Waiting etc

There's 2 issues:
When I run the LIKE part alone - returns 100's of duplicates. (but works)
When I use the full select statement as above it hangs mysql.

I'm not sure if I can use non-joined & joined parts together like this?