Damn! You're right!! I only looked at the subquery itself, not the context and I missed that his "subquery" seems to an expression to get part of his table name! That is definitely not valid in any dialect of SQL that I know ;-)
I wonder if the query itself is mistyped? Maybe he missed the last half of the table name and the left hand side of the subquery? I've never seen anyone try anything like that before in years of teaching SQL to beginners.... Rhino -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 14, 2005 1:01 PM To: admin Cc: mysql@lists.mysql.com Subject: Re: How to write subqueries? 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
