MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Rhino Date:June 14 2005 5:24pm
Subject:RE: How to write subqueries?
View as plain text  
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: SGreen@stripped [mailto:SGreen@stripped]
Sent: Tuesday, June 14, 2005 1:01 PM
To: admin
Cc: mysql@stripped
Subject: Re: How to write subqueries?


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