Self-join an alias table 1

[mayamanako]

hi guys, i am trying to self-join an alias table and i cant seem to get my head round it. can you advice please?

this is my sql:

SELECT id, description FROM (SELECT id, description
FROM adminTable) AS tempTable
INNER JOIN tempTable tempTable2 ON tempTable.id = tempTable2.id

it says:
invalid object name 'tempTable'

any advice guys? thanks.

 

[mayamanako]

hi guys, sorry if i was not specific in my post above. i should have also added that what i am trying to do here is if i can self-join the 'tempTable' to itself.

thanks for any advice.

 

[dhulbert]

SELECT *
FROM (SELECT id, description
FROM adminTable) AS tempTable
INNER JOIN
(SELECT id, description
FROM adminTable) AS tempTable2
on
tempTable.id = tempTable2.id


I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[mayamanako]

thanks dhulbert, that did the trick!

 

[kjv1611]

Would that be the same as this?

SELECT * FROM tempTable
UNION
SELECT * FROM tempTable

If so, wouldn't the UNION be more efficient, or am I missing something? I realize it's highly possible I'm missing something