Jump to content

temporary tables and Unions


ajoo
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
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
Link to comment
Share on other sites

  • 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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Hi Psycho,

 

Yes you were right. :happy-04:  Also the temporary table does not show in the phpadmin tables. :confused:  However queries excuted against the tmp table work !! So the table is created and somewhere there but not visible !! 

 

Thank you.

Link to comment
Share on other sites

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.