UNION query doesn't work...?

Dave · 09-20-2008, 07:22 PM

Hi,

I discovered to my chagrin that MySQL does not do FULL OUTER JOINs. (Does SQLite?)

At any rate, after much researching, I have come to believe that the closest I will come to the FULL OUTER JOIN is the UNION query. In the code below, each of the queries works alone, but when I try to join them with the UNION clause, I recieve an error saying that "can't reopen temp2".

Bear in mind that these are temporary tables.

Code:

SELECT temp1.last_name
   FROM temp1 LEFT OUTER JOIN temp2
        ON temp1.student_id = temp2.student_id  
UNION
SELECT temp2.last_name
    FROM temp2 LEFT OUTER JOIN temp1
         ON temp2.student_id = temp1.student_id ;

I've tried parens, etc. Nothing helps. Thanks for any advice!

Dave