Hi,
In alot of cases you can use the following query structure (extract from
MySQL 4.0.0alpha manual)to negotiate the need for Sub-Selects.
I hope this helps.
-Rich
1.4.4.1 Sub-selects
MySQL currently only supports sub selects of the form INSERT ... SELECT ...
and REPLACE ... SELECT .... You can however use the function IN() in other
contexts.
In many cases you can rewrite the query without a sub-select:
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
This can be re-written 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 sub-selects). For
this situation there are two options available until subqueries are
supported by MySQL.
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 contruct 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:
prompt> mysql --skip-column-names mydb < myscript.sql | mysql mydb
MySQL 4.0 supports multi-table deletes that can be used to efficiently
delete rows based on information from one table or even from many tables at
the same time.
-----Original Message-----
From: Christian Andersson [mailto:[EMAIL PROTECTED]]
Sent: 20 December 2001 19:36
To: Larry Reiter; MySQL Mailing list
Subject: Re: Nested Queries.
Subqueries are not (yet)working in mysql, they will be implemented in the
4.0.x or is it 4.x
which hopefully will be out very soon :-)
so if you are using the 3.23.xxx you have to stick with temporary tables
/Christian
----- Original Message -----
From: "Larry Reiter" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, December 20, 2001 7:58 PM
Subject: Nested Queries.
> I have constructed a query which requires a temporary table but I
> would rather use a nested query. In other words, I want to extract a
> set of data and then use that set of data as a query against some
> other data. Like some sort of SELECT within a SELECT. Can I do this
> in MySQL?
>
> Cheers,
>
> LTR
> --
> Dr. Lawrence T. Reiter
> Postdoctoral Fellow
> Department of Biology, UCSD
> Bonner Hall Room 4221
> 9500 Gilman Drive
> La Jolla, CA 92093-0349
>
> Phone: (858) 534-0442
> FAX: (858) 822-2044
> http://homophila.sdsc.edu
> email: [EMAIL PROTECTED]
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
