Jason For what you want to do I would think you need to use UNION instead of Outer Join or create a temporary table and insert the rows from both tables into the temporary table.
Buddy -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Jason Kramer Sent: Wednesday, September 24, 2008 9:12 AM To: RBASE-L Mailing List Subject: [RBASE-L] - FULL OUTER JOIN question I have two tables, one with active records and one with inactive records, and the columns in each table have the same name. They look like this: ACTIVE INACTIVE idnum fname idnum fname 1 John 3 Mary 2 Diego 6 Zhou 5 George 4 Jennifer I want to get a list of all idnums, active and inactive, like this: idnum ------ 1 2 3 4 5 6 I kept trying to use a FULL OUTER JOIN like this: SELECT idnum FROM active FULL OUTER JOIN inactive ON idnum I kept getting just idnums from the first table (active in this case), and none from the second. I tried many different variations with different ON clauses, but I could not get results form the second table. Next, I used a UNION: SELECT idnum FROM active UNION SELECT idnum FROM inactive That gave me the results I was looking for. I thought that the UNION statement above was equal to the FULL OUTER JOIN. Have I misunderstood how the FULL OUTER JOIN works? Thanks, Jason -- Jason Kramer University Archives and Records Management 002 Pearson Hall (302) 831 - 3127 (voice) (302) 831 - 6903 (fax) --- RBASE-L ================================================ TO POST A MESSAGE TO ALL MEMBERS: Send a plain text email to [email protected] (Don't use any of these words as your Subject: INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH, REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP) ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [email protected] In the message SUBJECT, put just one word: INTRO ================================================ TO UNSUBSCRIBE: Send a plain text email to [email protected] In the message SUBJECT, put just one word: UNSUBSCRIBE ================================================ TO SEARCH ARCHIVES: Send a plain text email to [email protected] In the message SUBJECT, put just one word: SEARCH-n (where n is the number of days). In the message body, place any text to search for. ================================================
