"Andrew Braithwaite" <[EMAIL PROTECTED]> wrote on 12/06/2005 11:34:50
AM:
> Hi,
>
> I'm having a problem with subqueries in MySQL 4.1.14 running on Fedore
> core 3.
>
> mysql> create table day_6_12_2005 (f1 int(1), f2 char(4));
> Query OK, 0 rows affected (0.04 sec)
>
> mysql> insert into day_6_12_2005 values(1,'test');
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select * from (select date_format(now(),'day_%e_%c_%Y')) as t1;
> +-----------------------------------+
> | date_format(now(),'day_%e_%c_%Y') |
> +-----------------------------------+
> | day_6_12_2005 |
> +-----------------------------------+
> 1 row in set (0.04 sec)
>
> mysql> select f1,f2 from (select date_format(now(),'day_%e_%c_%Y')) as
> t1;
> ERROR 1054 (42S22): Unknown column 'f1' in 'field list'
>
> Any one know what's going on? According to the docs, this should work
> fine...
>
> Any pointers or ideas will be much appreciated...
>
> Cheers,
>
> Andrew
>
> SQL, query
>
MySQL does not have fully-functional, dynamic SQL, yet. What you did is
called an "anonymous view" or a "derived table". There currently is no way
(outside of a procedure or function and using prepared statements) to
build a string and attempt to execute it as a SQL statement with just
MySQL. You can do this in a programming language and submit the string
just as you would any other query but you generally cannot build that
string within MySQL and execute it from within MySQL (yet).
How what you did normally works like this. Yes, there are other ways to
solve this problem but this demonstrates the principle behind the
anonymous view.
__setup__
Imagine you have a table called `stat_totals` that has the columns
`test_id`, `total_items`, and `run_count`. You have been asked to produce
a count of how many tests average at least 10 items per run.
__example solution__
It's trivial to find the average number of items per test with a query
like this:
SELECT
test_id
,if(`run_count`>0,`total_items`/`run_count`,NULL) as avg_per_run
FROM `stat_totals`;
That query produces two columns of output: `test_id` and `avg_per_run`.
Wouldn't it be nice to be able to directly query the output of the
previous statement? You can if you use it as an anonymous view. This will
return all rows form the previous output where avg_per_run is greater than
10:
SELECT
test_id
,avg_per_run
FROM (
SELECT
test_id
,if(`run_count`>0,`total_items`/`run_count`,NULL) as avg_per_run
FROM `stat_totals`;
) as avg_table
WHERE avg_table.avg_per_run > 10;
When you did this same query style within your original query, you only
had one column in your one-row, dynamically declared table called
`date_format(now(),'day_%e_%c_%Y')` and it had the single value of
'day_6_12_2005' (see your previous query) which is why the query engine
could not find the column `f1` in that "table" (actually your query
results) and threw the error. Anonymous views became possible with the
UNION changes added to 4.0.
__end example__
Make better sense?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
