query works on various servers but i get error messages on my host ser 1

[pushyr]

hi,

this is the second time i've got this error message...

MySQL said:
#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 'SELECT itemnumber
FROM 00_orders_paypal_payment_info ) AND supp

and here is the code...

SELECT *
FROM 04_articles
WHERE unique_id0 NOT
IN
(
SELECT itemnumber
FROM 00_orders_paypal_payment_info
) AND supplier_id = 'YUTFHDNEEHGT12654' AND deleted = 'publish' OR deleted = 'approve'
LIMIT 0 , 30

the query works fine on various servers and my local server running mysql-5.0.45.

but it doesn't work on my host server. i've told them the same thing but they only tell me that there must be something wrong with my script and thats the farthest i can get with them. they couldn't even reply back to tell me what version of mysql they are running. this is the second time a query has produced an error on their server which i know works fine elsewhere. i just can't ignore this any longer.

is there something wrong with my query? or is there a fix that i could apply to make it work?

much appreciated

 

[vacunita]

If it works on as many other servers as you say, then its most likely a problem with your host. I would venture the have an older version of MYSQL that doesn't support the syntax you are using.

If they won't answer you with at least what version they have, then i would start looking for another host.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[pushyr]

just found out the version of mysql is 4.0. any tips on modifying the query to work with this version?

 

[vacunita]

I'm no expert, but this page might be of help.

You would need to change the nested select statement into a Join for it to work in older versions of MYSQL.

This page may help you a it, until someone more knowledgeable than me in joins comes along.

http://dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[r937]

i don't see how it can possibly "work fine" because you seem to have a logic error in combining your ANDs and ORs, which i have fixed in the query below

if you need to, you can unfix it

SELECT 04_articles.*
  FROM 04_articles
LEFT OUTER
  JOIN 00_orders_paypal_payment_info
    ON 00_orders_paypal_payment_info.itemnumber = 04_articles.unique_id0
 WHERE 04_articles.supplier_id = 'YUTFHDNEEHGT12654' 
   AND 04_articles.deleted IN ('publish','approve')
   AND 00_orders_paypal_payment_info.itemnumber IS NULL
 LIMIT 0,30

r937.com | rudy.ca

 

[pushyr]

hey vacunita and r937! thanks for your input. r937, it worked and i can definitely learn a thing or two from you!

Thanks!!