Stack Exchange
mysql temporary table in subquery
I'm trying to use temp table in subquery (stored procedure), but it returns empty result set...
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_profile_with_templates`(IN _username NVARCHAR(50), IN _template NVARCHAR(50))
BEGIN
    SELECT id INTO @template FROM profile_template WHERE name=_template LIMIT 1;
    SELECT * FROM profile WHERE username=_username AND template=@template LIMIT 1;
    CREATE TEMPORARY TABLE IF NOT EXISTS sections SELECT * FROM profile_template_section WHERE template=@template;
    **CREATE TEMPORARY TABLE IF NOT EXISTS components SELECT * FROM component_template WHERE section IN (SELECT id FROM sections);**
    SELECT * FROM sections;
    SELECT * FROM components;
    SELECT * FROM component_template_option WHERE component_template IN (SELECT id FROM components);
END

the query between the **'s is the one returns empty... 
if I run the same query on the real table, It returns values...
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_profile_with_templates`(IN _username NVARCHAR(50), IN _template NVARCHAR(50))
BEGIN
    DROP TEMPORARY TABLE IF EXISTS sections;
    DROP TEMPORARY TABLE IF EXISTS components;
    SELECT id INTO @template FROM profile_template WHERE name=_template LIMIT 1;
    SELECT * FROM profile WHERE username=_username AND template=@template LIMIT 1;
    CREATE TEMPORARY TABLE IF NOT EXISTS sections SELECT * FROM profile_template_section WHERE template=@template;
    CREATE TEMPORARY TABLE IF NOT EXISTS components SELECT * FROM component_template WHERE section IN (SELECT id FROM sections);
    SELECT * FROM sections;
    SELECT * FROM components;
    SELECT * FROM component_template_option WHERE component_template IN (SELECT id FROM components);
    DROP TEMPORARY TABLE IF EXISTS sections;
    DROP TEMPORARY TABLE IF EXISTS components;
END


Are you making sure the temporary tables are dropped between executions on the same connection? CREATE...IF NOT IS EXISTS..SELECT will not insert into the table if it already exists.

so should I add drop table in the end of the procedure?

Adding DROP TEMPORARY TABLE IF EXISTS tablename; queries to the start and end of the procedure for each temporary table created by the procedure usually keeps things going smoothly. (Technically only needed once, but if something goes wrong in a call they could get stuck if you only have the drops at the end; and if they're only at beginning, they would be left hanging around around after the call.)

Okay, now it works, but when I receive the data with node js and changing the parameters, the data stays the same (which sounds like the table is not dropped)...

You have multiple selects (without into's), which will cause the procedure to return multiple result sets (looks like four); are you handling those appropriately? Also, don't lean too heavily on @ variables; they are not private and can carry over values from previous executions.

It's like that in the mysql console too.. it might be the variable, you're right... how do I drop this variable too?

DECLARE one in the procedure instead.

Thank's!! you solved it :) how can I vote for your answer? I can't find the vote button...

I'll put an actual answer, these have just been comments.

I don't have enough reputation to vote apparently, I hope someone else will vote for you :)

When using CREATE ... SELECT statements, the SELECT portion will not populate the table if it already exists. 
If creating temporary tables in a stored procedure that will not be used outside of the procedure, it is best to use DROP TEMPORARY TABLE IF EXISTS statements at the start and end of the procedure. The ending ones are so the procedure cleans up after itself; the starting ones ensure temporary tables left over from previous executions (that errored/failed before dropping them) do not interfere with current executions. You could also wrap the main body of the procedure in a TRY, and have the drops outside of the TRY, to ensure the drops occur, but that is a bit more advanced.
When using session/user/@ variables, keep in mind they are global for the database connection and can carry over values; when possible use locally DECLAREd variables (whose scope does not go beyond the procedure). Bonus: Also, try to make sure the names of such variables, and procedure parameters, are not ambiguous with field names of the tables used in the procedure; it can cause very hard to diagnose issues.
