SQL - AS - table doesn't exist - 1146

Question

My query is:

SELECT temp.pid FROM 
(SELECT postid, date FROM swapping AS s, post AS p 
WHERE s.mid='2' AND p.postid=s.postid)  AS temp 
WHERE temp.date = (SELECT MAX(date) FROM temp)

I receive #1146 - Table 'databasename.temp' doesn't exist

How can I make it work? Thank you.

Answer 1

It seems like you want to select the last "pid", in terms of "date", where s.mid='2'

Try this (after you figure out where pid comes from and correct the first line)

SELECT [s? or maybe p?].pid
FROM swapping s INNER JOIN post p ON p.postid=s.postid
WHERE s.mid = '2'
ORDER BY date DESC
LIMIT(0,1)

You might also need to alias the date column in the order by line.