Select from table with IF

Question

I am working on some reports and I am trying to create a query that would select a different query depending on the date selected, this is a simplify version of what I am trying to achieve

SELECT *
FROM
    IF(YEAR('###date start###'=2011),table_2011,table)
WHERE
    date_start BETWEEN '###date start###' AND '###date end###'

###date start### and ###date end### are parameters entered by the user.

I cannot use the language to select the different table depending on the date as this is only an example and I might have other parameters passed that would select a different table.

I am thinking that I will need to create a new preg_replace and force the person that create the queries to add something like ###if(test,val2,val2)### but if mysql can already do this I would not like to re-invent the hot-water.

Thank you

EDIT

While waiting for a better solution, I changed the "search replace" function from a simple: ###search### => value (where the ###search### was showing to the end user as a field to manually enter), to also accept a new field formatted like LLLlabel[option:value,option:value,option:value,....]LLL

Not the best solution as I wish I could do it with MySQL only as this solution is only an annoyance to the end user to have to select an extra parameter.

Please keep in mind that the question is only a VERY simplified version of the query, the query can be part of subqueries, or unions, or joins and not only have a time separation

possible duplicate of [How to select from MySQL where Table name is Variable](http://stackoverflow.com/questions/8809943/how-to-select-from-mysql-where-table-name-is-variable) — Manse, Nov 12 '12 at 14:42
the table splitting is just an example, sometime is yearly splitting, other times is a different type of table division. Some tables are milions of records with over 1milion added each month. manseUK, yes, that is exactly what I want to do, but I can run only one query at the time, so that solution won't work for me — Fabrizio, Nov 12 '12 at 15:05

Ross Smith II · Accepted Answer · 2012-11-16T18:03:28.820

1

A stored procedure with the following will work:

DELIMITER //

CREATE PROCEDURE example(date_start DATETIME, date_end DATETIME)
BEGIN
    SET @table = IF(YEAR(date_start)=2011,'table_2011','table');

    SET @sql = CONCAT(
        "SELECT * FROM ",
        @table,
        " WHERE date_start BETWEEN ",
        QUOTE(date_start),
        " AND ",
        QUOTE(date_end)
    ); 

    PREPARE stmt FROM @sql; 
    EXECUTE stmt; 
    DEALLOCATE PREPARE stmt; 
END;
//

DELIMITER ;

and call it via:

CALL example('###date start###', '###date end###');

or if your data is in MyISAM tables, you could set up a MERGE table:

CREATE TABLE all_tables (...) ENGINE = MERGE UNION=(table_2011, table);

SELECT *
FROM
    all_tables
WHERE
    date_start BETWEEN '###date start###' AND '###date end###'

edited Nov 16 '12 at 18:03

answered Nov 12 '12 at 14:54

Ross Smith II

11,799
1
38
43

This would be great, but I can only run one query meaning I will have to create the Stored Procedure before the execution of the query then drop it, similar to this http://stackoverflow.com/questions/8809943/how-to-select-from-mysql-where-table-name-is-variable – Fabrizio Nov 12 '12 at 15:10
Sorry, I don't understand. Please elaborate on what the issue is you are having. – Ross Smith II Nov 12 '12 at 19:33
I am using a legacy system that can only execute one query at the time, the query is pulled from a database and executed. I will have to modify quite a bit of code to read multiple queries. It also uses a library that doesn't not support multiquery – Fabrizio Nov 12 '12 at 23:19
Sorry, I don't see how only being able to execute one query at a time has any bearing on this solution. – Ross Smith II Nov 16 '12 at 18:06

Olaf Dietsche · Answer 2 · 2012-11-12T18:45:40.670

0

This might work, if the tables are disjoint:

select *
from (select * from table_2011
      union
      select * from table) x
where date_start BETWEEN '###date start###' AND '###date end###';

Update:

If size is your concern, you should look into partitioning your table. See

Partitioning
RANGE Partitioning, look for PARTITION BY RANGE (YEAR(separated))

edited Nov 12 '12 at 18:45

answered Nov 12 '12 at 14:51

Olaf Dietsche

72,253
8
102
198

if I do that, the resulting table (as I have over 22 IFs) would be almost 100Milion records – Fabrizio Nov 12 '12 at 15:06
the queries have millions of records and are partition when possible. Some tables can't be partition because, for example, the queries are done always done using a date range causing the engine to union the partition tables and slow down incredibly (I have tested the partitioning, had to revert it on almost all of my tables) – Fabrizio Nov 12 '12 at 23:21

Select from table with IF

2 Answers2