Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    13

    Unanswered: Selecting from a table name retrieved from a subquery

    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.

  2. #2
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    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

  3. #3
    Join Date
    Jan 2006
    Posts
    7

    Dynamic query with PostgreSQL

    Hi there,

    I have also been wondering why the following query doesn't work in PostgreSQL:

    insert into store_to_storecatagory
    values (select distinct storeid from store where store ILIKE 'Art etc',
    select distinct storecatagoryid from storecatagory where
    storecatagory ILIKE 'ARTS AND CURIOS');

    The response I get is:
    ERROR: syntax error at or near "select" at character 44

    I know that the SELECT statements work fine. But I am not sure that they can be combined with an INSERT statement...


    Your help is always appreciated. Thanks and best wishes,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •