[Zope-DB] Re: subqueries from ZSQL method?
Ole J. Tennvassås
olejt at tiscali.no
Tue Sep 7 14:37:47 EDT 2004
> > 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
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
Works fine! Also managed to do the same by tying two SQL methods
together with a Python script, but this is more compact.
More information about the Zope-DB