Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    Post Unanswered: Mysql Variables - Little Question

    Hi,

    I have to do several queries that stores data in some temporary tables,
    For almost each, I need to filter using a certain preselection,
    let me give you an example:

    Code:
    DROP TEMPORARY TABLE IF EXISTS tmp_data1;
    
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_data
    SELECT
     src.id AS "id"
     , src.name AS "name"
     , 1 AS "source"
    FROM
     table_source1 AS src
    WHERE
     src.filter IN (1, 3, 5, 16, 21)
    ;
    
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_data
    SELECT
     src.id AS "id"
     , src.name AS "name"
     , 2 AS "source"
    FROM
     table_source2 AS src
    WHERE
     src.filter IN (1, 3, 5, 16, 21)
    ;
    
    SELECT * FROM tmp_data;
    Is there a way to use a MySQL variable to store the filters ?
    By doing something like that:
    Code:
    @filters = array{1, 3, 5, 16, 21};
    ...
    WHERE
     src.filter IN @filters
    ...
    Otherwise I was thinking to use another temporary table with
    the filters in it but I'm not sure that is the good solution:
    Code:
    DROP TEMPORARY TABLE IF EXISTS tmp_filters
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_filters
     (filter INT, PRIMARY KEY (filter))
    SELECT 21 AS "filter";
    
    INSERT INTO tmp_filters (filter) VALUES((1), (3), (5), (16), (21));
    
    ...
    FROM
     table_source2 AS src
     JOIN tmp_filters AS flt ON (src.filter = ftl.filter)
    ;
    ...

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    I don't think you can do that with a variable. Why do you need to store it and not do it at query time?

    By the way why are you creating temp tables and not doing a simple union?

    Code:
    SELECT * FROM
    (
    SELECT
     src.id AS "id"
     , src.name AS "name"
     , 1 AS "source"
    FROM
     table_source1 AS src
    WHERE
     src.filter IN (1, 3, 5, 16, 21)
    
    UNION 
    
    SELECT
     src.id AS "id"
     , src.name AS "name"
     , 2 AS "source"
    FROM
     table_source2 AS src
    WHERE
     src.filter IN (1, 3, 5, 16, 21)
    ) AS derived_table

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the whole idea of using a temporary table needs to be looked at

    i don't think you need one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    This was just an example.
    The question is not to know if I need to create temporary tables
    for storing my data, but to find a mean to use a "common filter".
    The aim is not to have to put the filter each time with
    src.filter IN (1, 3, 5, 16, 21) for maintenance reason.
    Actually I could merge the queries in one
    but I can't tell MySQL how to optimize the JOINs here
    So the splitted version is 100x quicker.

    Let me enclose you a file with the true queries for info.
    Attached Files Attached Files

  5. #5
    Join Date
    Mar 2004
    Posts
    480
    Why do you have separate tables in the first place rather than a column in a single table denoting which of your current tables the data belongs in? I don't know your data exactly, but it seems to me it should be in a single table.

    As for the variables, like I said, within mysql I don't think you can do that because you can't store variables like an array. If you were using PHP or PERL or whatever, then you could use that, explode it and search on it.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gtk
    This was just an example.
    in that case, my reply was just an example too

    but you still don't need a temporary table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2007
    Posts
    4
    Storing the values in another table would do. With a table called filters with a single column filtervalue, and using something like this:

    Code:
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_data
    SELECT
     src.id AS "id"
     , src.name AS "name"
     , 2 AS "source"
    FROM
     table_source2 AS src
    WHERE
     src.filter IN (SELECT filtervalue FROM filters)
    ;
    I tested it in my db and worked...

Posting Permissions

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