on 3/6/03 4:59 PM, Katie Evans-Young at [EMAIL PROTECTED] appended the
following bits to my mbox:

> I need to only return a row from table1 if its id is not found in table2.
> The query below is how I think it should work. However (as I had imagined),
> this doesn't work. Syntax error (you can't put a select inside a select like
> that).

> SELECT table1.id FROM table1 WHERE table1.sold=1 AND table1.id NOT IN
> (SELECT table2.table1_id FROM table2)

> I went through all of the functions to use in a WHERE clause in my MySQL
> book...am I missing something?

Unfortunately, MySQL doesn't support sub selects so that syntax won't work.
You have a couple other options in MySQL:

1) Create a new temporary table with the results of the second query. Then
perform the first query on that temporary table.  Then delete the temporary

2) Rewrite the query with a JOIN, something like:

SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id=table2.table1_id
WHERE table1.sold=1 AND table2.table1_id IS NULL; (not tested)

More info can be found here:


Hope that helps.


Paul Burney

Q: Tired of creating admin interfaces to your MySQL web applications?

A: Use MySTRI instead. Version 3.1 now available.

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to