#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    18
    Rep Power
    0

    Variable table name in SELECT statement


    What is the way to write a SELECT statement to retrieve data from several tables with the same name pattern. I'd like to select data from these tables: table_2012, table_2013, table_2014... I only want the tables that have the prefix (e.g. table_) and exactly the four characters for the year. I don't want tables that have a different pattern than the year. Is this doable in MySQL? What is the

    SELECT * FROM TABLE LIKE table_% WHERE ....
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,294
    Rep Power
    9400
    Not like that no.

    The real problem is that you have a bunch of tables all for specific years. You really should be storing everything in one table, and including an extra column to indicate the year (if there isn't already something you can use).

    You may be able to create VIEWs for each of the tables you're deprecating.
    Code:
    CREATE VIEW `table_2012` AS SELECT * FROM `table` WHERE `yearValue` = 2012;
    If that doesn't work I'd even consider using triggers (per-year tables have INSERT/UPDATE triggers to copy the action in the main table, main table has basically the same but going into the per-year tables) just so that you can collect all the data in one master location.


    But to answer the question you can do a UNION between all of them. (Still have to write the queries though.)
    Code:
    SELECT ...
    UNION ALL
    SELECT ...
    UNION ALL
    SELECT ...
    With a "UNION" alone MySQL will try to remove duplicates for you. If you want them, or know that there won't be any, "UNION ALL" will instruct it not to do so.
    Last edited by requinix; December 21st, 2012 at 09:53 PM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    18
    Rep Power
    0
    These are all good suggestions. But I was wondering whether there was a way to define the SELECT statement to look for tables with a certain pattern in their name, similar to the LIKE feature for columns.

    Thanks.


    Originally Posted by requinix
    Not like that no.

    The real problem is that you have a bunch of tables all for specific years. You really should be storing everything in one table, and including an extra column to indicate the year (if there isn't already something you can use).

    You may be able to create VIEWs for each of the tables you're deprecating.
    Code:
    CREATE VIEW `table_2012` AS SELECT * FROM `table` WHERE `yearValue` = 2012;
    If that doesn't work I'd even consider using triggers (per-year tables have INSERT/UPDATE triggers to copy the action in the main table, main table has basically the same but going into the per-year tables) just so that you can collect all the data in one master location.


    But to answer the question you can do a UNION between all of them. (Still have to write the queries though.)
    Code:
    SELECT ...
    UNION ALL
    SELECT ...
    UNION ALL
    SELECT ...
    With a "UNION" alone MySQL will try to remove duplicates for you. If you want them, or know that there won't be any, "UNION ALL" will instruct it not to do so.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by mmmosias
    But I was wondering whether there was a way to define the SELECT statement to look for tables with a certain pattern in their name, similar to the LIKE feature for columns.
    there isn't

    you could do it with an application language like php, by reading the information_schema tables to build the query, but that's two separate steps
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo