fidget права - одним запросом не получится (с select попутал немного), есть правда один извращенный способ:
Sub`SELECT\'s
............
MySQL Server until version 4.0 only supports nested queries of the form
`INSERT ... SELECT ...\' and `REPLACE ... SELECT ...\'. You can,
however, use the function `IN()\' in other contexts. Subselects are
being implemented in the 4.1 development tree.
Meanwhile, you can often rewrite the query without a subselect:
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
This can be rewritten as:
SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;
The queries:
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2
WHERE table1.id=table2.id);
Can be rewritten as:
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;
For more complicated subqueries you can often create temporary tables
to hold the subquery. In some cases, however, this option will not
work. The most frequently encountered of these cases arises with
`DELETE\' statements, for which standard SQL does not support joins
(except in subselects). For this situation there are two options
available until subqueries are supported by MySQL Server.
The first option is to use a procedural programming language (such as
Perl or PHP) to submit a `SELECT\' query to obtain the primary keys for
the records to be deleted, and then use these values to construct the
`DELETE\' statement (`DELETE FROM ... WHERE ... IN (key1, key2, ...)\').
The second option is to use interactive SQL to construct a set of
`DELETE\' statements automatically, using the MySQL extension `CONCAT()\'
(in lieu of the standard `||\' operator). For example:
SELECT CONCAT(\'DELETE FROM tab1 WHERE pkid = \', "\'", tab1.pkid, "\'", \';\')
FROM tab1, tab2
WHERE tab1.col1 = tab2.col2;
You can place this query in a script file and redirect input from it to
the `mysql\' command-line interpreter, piping its output back to a
second instance of the interpreter:
shell> mysql --skip-column-names mydb < myscript.sql | mysql mydb
