Quote:
|
Originally Posted by localhost
I am wondering if this is conceptually possible:
SELECT A.x FROM ( SELECT y FROM z ) A;
Essentially the table that the outer query is accessing would be determined by a query returning a single value (varchar).
Is there another way to do this and/or is this correct? I tried this with a very simple implementation (two tables, int and varchar) and the query acted as if it didn't actually select anything from the desired table.
|
As of MySQL v 4.1, the syntax is correct, but it doesn't work the way you want it to work.
Maybe the following example can clarify this:
Code:
USE test;
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
id INT UNSIGNED NOT NULL PRIMARY KEY,
c CHAR(1) NOT NULL
);
INSERT INTO foo VALUES (1, 'a'), (2, 'b'), (3, 'c');
SELECT bar.id, bar.cmany
FROM (SELECT id, REPEAT(c, id) cmany FROM foo) bar;
The subquery creates a so-called
unnamed view or
derived table which is then used by the outer query.
From MySQL 4.1, you can use dynamic SQL through prepared statements to do what you want:
Code:
DROP TABLE IF EXISTS z;
CREATE TABLE z ( y CHAR(10) NOT NULL);
INSERT INTO z (y) VALUES ('foo');
SET @stmt := CONCAT("SELECT A.id, A.c FROM ", (SELECT y FROM z ), " A");
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
--
felix
dBforums
>
Selecting from a table name retrieved from a subquery
12-21-05, 21:56
Linear Mode
