[Zope-DB] Re: subqueries from ZSQL method?

[Wolfram Kraus]

Ole J. Tennvassås wrote:
> I'm trying to run this SQL statement:
> "SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2 WHERE s2 =
> <dtml-sqlvar s2 type="int">)",
> but zope gives the error message:
> Error, _mysql_exceptions.ProgrammingError: (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 s1 FROM t2 WHERE s2 =
> 2) LIMIT 1000")
> What i want to do is list all values of s1 in table 1 which isnt found
> in table 2 rows where s2  some dynamic value.
> If i run the subquery only it works fine.
> Any suggestions?
> 

Which MySQL version do you use? To run subqueries you need version 
4.1.x. With Mysql 4.0.x or 3.x you have to rewrite your query, instead 
of a subquery with NOT IN you need a LEFT JOIN, see the MySQL-documentation.

HTH,
Wolfram