JOIN works in MySql 4.0 but not 3.2

kk7064

hi, i have the follow query in php code

SELECT main.*
FROM main JOIN modeln5 ON main.mn5 = modeln5.name
WHERE CASE WHEN 'm1' LIKE 'all' THEN main.mn1 LIKE '%' ELSE main.mn1 LIKE 'm1' END AND CASE WHEN 'm2' LIKE 'none' THEN main.mn2 IS NULL WHEN 'm2' LIKE 'all' THEN main.mn2 LIKE '%' ELSE main.mn2 LIKE 'm2' END AND CASE WHEN 'm3' LIKE 'none' THEN main.mn3 IS NULL WHEN 'm3' LIKE 'all' THEN main.mn3 LIKE '%' ELSE main.mn3 LIKE 'm3' END AND CASE WHEN 'm4' LIKE 'none' THEN main.mn4 IS NULL WHEN 'm4' LIKE 'all' THEN main.mn4 LIKE '%' ELSE main.mn4 LIKE 'm4' END AND CASE WHEN 'm6' LIKE 'none' THEN main.vac IS NULL WHEN 'm6' LIKE 'all' THEN ( main.vac LIKE '%' OR main.vac IS NULL) ELSE main.vac LIKE 'm6' END AND CASE WHEN 'm5' LIKE 'H' THEN modeln5.name LIKE '___H' WHEN 'm5' LIKE 'all' THEN modeln5.name LIKE '%' ELSE modeln5.spec LIKE 'm5' END

it works well in my localhost with MySql 4.0 , but doesnt work in remote server with MySql 3.2 which gives out the syntax error near "ON main.mn5 = modeln5.name WHERE CASE WHEN 'm1' LIKE....

i check my query by removing the join statement, ie, simply using select from main, modeln5 .....followed by original stuffs. It gives no errors.

Is there any way to solve the problem? I am new in database and I only know JOIN ... ON .... gives me the right result (in my localhost).

Thank you very much

mrhappiness

kk7064 wrote:
simply using select from main, modeln5 .....followed by original stuffs. It gives no errors.

and produces the desired results?

if not try using INNER JOIN or LEFT JOIN instead

btw:

http://dev.mysql.com/doc/mysql/en/join.html
Note that INNER JOIN syntax allows a join_condition only from MySQL 3.23.17 on. The same is true for JOIN and CROSS JOIN only as of MySQL 4.0.11.

that seems to be the reason why you get this error with mysql 3.x

kk7064

🙂 HI Mr. Happiness, thank you very much.

INNER JOIN works very well.

It took me forever to google for solution this morning. Your answer saved me a lot of time.

P.S. this does not produce correct result but only the right syntax:

Originally Posted by kk7064
simply using select from main, modeln5 .....followed by original stuffs. It gives no errors.