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

[Ole J. Tennvassås]

> > 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
> 

This was exactly the problem: My version is 4.0.18.

SELECT t1.s1 FROM t1 LEFT JOIN t2 ON t1.s1 != t2.s1 WHERE s2 = (some
value)

Works fine!  Also managed to do the same by tying two SQL methods
together with a Python script, but this is more compact.
Thanks!

Ole