admin <[EMAIL PROTECTED]> wrote on 06/14/2005 01:01:33 PM:
> Hello!
> MySQL v. 4.1.11.
> I'm trying to write a subquery, MySQL says "Error".
> select num, theme, intro
> from vt_(select pnid from vt_partition where pnid regexp '^[0-9]{11}$')
> order by date desc, timer desc;
> describe vt_parition:
> +----------+--------------+------+-----+----------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +----------+--------------+------+-----+----------+----------------+
> | num | int(11) | | MUL | NULL | auto_increment |
> | partname | varchar(255) | | | | |
> | pnid | varchar(11) | | | | |
> | timer | time | | | 00:00:00 | |
> +----------+--------------+------+-----+----------+----------------+
> describe vt_24411620611;
> +-------+--------------+------+-----+------------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +-------+--------------+------+-----+------------+----------------+
> | num | int(11) | | MUL | NULL | auto_increment |
> | login | varchar(10) | | | | |
> | mail | varchar(35) | | | | |
> | theme | varchar(100) | | | | |
> | intro | text | | | | |
> | text | text | | | | |
> | date | date | | | 0000-00-00 | |
> | timer | time | | | 00:00:00 | |
> +-------+--------------+------+-----+------------+----------------+
> "vt_" is a prefix, "pnid" is a postfix.
> And the name of the table is, for expamle, vt_01234567890.
> Where is/are the mistake(s) in my subquery?
> Please explain me how to write a subquery in the right way.
> --
> Good luck!
> Vladimir
First off, you are not writing a subquery. You are attempting to build a
SQL query using the results of another query. What you are attempting is
dynamic SQL. I am not familiar with a mechanism in 4.1.11 that allow you
to execute a string (or a string variable) from within a SQL statement.
The 5.0 versions have stored procedures, stored functions, and prepared
statements. Any one of those may be a solution to your query production
problem.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
