ajoo Posted August 9, 2016 Share Posted August 9, 2016 Hi all ! while this query (with table names shortened for clarity) works great and returns a result set just fine, SELECT mr.st_id as st_id from mr,ar where mr.mid = ar.st_id and ar.aid = 1 UNION SELECT ar.st_id as st_id from ar WHERE ar.aid = 1 && ar.role <> 'master' this following one executes and returns a zero result set. CREATE TEMPORARY TABLE tmp_result SELECT st_id from (( SELECT mr.st_id as st_id from mr,ar where mr.mid = ar.st_id and ar.aid = 1) UNION (SELECT ar.st_id as st_id from ar WHERE ar.aid = 1 && ar.role <> 'master')) tt I wish to use the result set obtained from the first query to join with another query a few lines away. Please suggest what is going wrong here & if this approach is fine and workable. Thanks all ! Quote Link to comment https://forums.phpfreaks.com/topic/301794-temporary-tables-and-unions/ Share on other sites More sharing options...
ajoo Posted August 9, 2016 Author Share Posted August 9, 2016 (edited) hmmm even this works and displays the result set correctly. SELECT st_id from (( SELECT mr.st_id as st_idfrom mr,arwhere mr.mid = ar.st_id and ar.aid = 1)UNION (SELECT ar.st_id as st_id from arWHERE ar.aid = 1 && ar.role <> 'master')) tt It's only the CREATE TEMPORARY TABLE ... bit that returns an empty result set ( zero rows ). Edited August 9, 2016 by ajoo Quote Link to comment https://forums.phpfreaks.com/topic/301794-temporary-tables-and-unions/#findComment-1535820 Share on other sites More sharing options...
Solution Psycho Posted August 9, 2016 Solution Share Posted August 9, 2016 I'm not sure, but you are using the SELECT statement to populate a temporary table. I don't think it is going to return the data. I would think you would then need to run a second query against that temporary table to retrieve the data. Quote Link to comment https://forums.phpfreaks.com/topic/301794-temporary-tables-and-unions/#findComment-1535838 Share on other sites More sharing options...
ajoo Posted August 9, 2016 Author Share Posted August 9, 2016 Hi Psycho ! Thank you for the response. Yes I did think about that but failed to see the temporary table tmp_result in the tables in phpmyadmin. So maybe it doesn't show out there at all ! I'll run a query against it anyways and revert. Thanks you. Quote Link to comment https://forums.phpfreaks.com/topic/301794-temporary-tables-and-unions/#findComment-1535846 Share on other sites More sharing options...
ajoo Posted August 9, 2016 Author Share Posted August 9, 2016 Hi Psycho, Yes you were right. Also the temporary table does not show in the phpadmin tables. However queries excuted against the tmp table work !! So the table is created and somewhere there but not visible !! Thank you. Quote Link to comment https://forums.phpfreaks.com/topic/301794-temporary-tables-and-unions/#findComment-1535848 Share on other sites More sharing options...
kicken Posted August 9, 2016 Share Posted August 9, 2016 It's called temporary for a reason. A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other CREATE TABLE manual page 1 Quote Link to comment https://forums.phpfreaks.com/topic/301794-temporary-tables-and-unions/#findComment-1535863 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.