Jump to content

temporary tables and Unions


Go to solution Solved by Psycho,

Recommended Posts

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 ! 

 

 

 

 

 

Link to comment
https://forums.phpfreaks.com/topic/301794-temporary-tables-and-unions/
Share on other sites

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 by ajoo
  • Solution

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.

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
  • Like 1
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.