temporary tables and Unions

[ajoo]

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 ! 

 

 

 

 

 

[ajoo]

hmmm even this works and displays the result set correctly.

 

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

 

 It's only the CREATE TEMPORARY TABLE ... bit that returns an empty result set ( zero rows ).  

Edited August 9, 2016 by ajoo

[Psycho]

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.

[ajoo]

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.

[ajoo]

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.

[kicken]

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