TalkPHP - View Single Post

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

09-20-2008, 07:22 PM	#1 (permalink)
Dave The Acquainted Join Date: Apr 2008 Posts: 110 Thanks: 97	UNION query doesn't work...? 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