List:General Discussion« Previous MessageNext Message »
From:SGreen Date:June 14 2005 5:01pm
Subject:Re: How to write subqueries?
View as plain text  
admin <bvbn@stripped> 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
Thread
How to write subqueries?Unknown Sender14 Jun
  • Re: How to write subqueries?SGreen14 Jun
    • RE: How to write subqueries?Rhino14 Jun
  • RE: How to write subqueries?Rhino14 Jun
  • Re: How to write subqueries?mfatene14 Jun
    • Re: How to write subqueries?Unknown Sender14 Jun
      • Re: How to write subqueries?mfatene14 Jun
        • Table fullEmmett Bishop15 Jun
          • Re: Table fullmfatene15 Jun
            • Re: Table fullEmmett Bishop15 Jun
              • Re: Table fullmfatene15 Jun
                • Re: Table fullEmmett Bishop15 Jun
                  • Re: Table fullmos16 Jun
                    • Re: Table fullEmmett Bishop16 Jun
                      • Re: Table fullmfatene16 Jun
        • Re: How to write subqueries?Unknown Sender15 Jun
    • Re: How to write subqueries?Unknown Sender14 Jun