on 3/6/03 4:59 PM, Katie Evans-Young at katie682@comcast.net 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
table.
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:
<http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html>
Hope that helps.
Sincerely,
Paul Burney
<http://paulburney.com/>
Q: Tired of creating admin interfaces to your MySQL web applications?
A: Use MySTRI instead. Version 3.1 now available.
<http://mystri.sourceforge.net/>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
