MySQL cant join against temp table

0
votes

Why can I not create a temporary table then immediately JOIN against it?

mysql> CREATE TEMPORARY TABLE table2 as (select in_reply_to_id, COUNT(in_reply_to_id) as C from mentions where in_reply_to_id>0 group by in_reply_to_id);

Query OK, 57149 rows affected (0.14 sec) Records: 57149 Duplicates: 0 Warnings: 0

mysql> select T.tweet_id, T.favorite_count, T.retweet_count, T2.C from 
-> tweets AS T JOIN table2 AS T2 
-> on T.tweet_id = T2.in_reply_to_id;

ERROR 1146 (42S02): Table 'twitter_analysis.table2 as' doesn't exist mysql>

But it does exist because I can select from it! 

select count(*) from table2;

57149 1 row in set (0.01 sec)

This error is extremely frustrating. Is there a way to put this temp table into the select query?

2
mysql

2 Answers

4
votes

you can just put it in the query like this

select T.tweet_id, T.favorite_count, T.retweet_count, T2.C 
FROM tweets T 
JOIN 
(   SELECT in_reply_to_id, COUNT(in_reply_to_id) as C 
    FROM mentions 
    WHERE in_reply_to_id>0 
    GROUP BY in_reply_to_id
) T2 on T.tweet_id = T2.in_reply_to_id;

you could try selecting from the temporary table and joining the other table to it.

1
votes

You could put them in one single query: 

select T.tweet_id, T.favorite_count, T.retweet_count, T2.C from 
tweets T JOIN (select in_reply_to_id, COUNT(in_reply_to_id) as C from mentions where in_reply_to_id>0 group by in_reply_to_id) T2 
on T.tweet_id = T2.in_reply_to_id;