This is a classic full outer join, as far as I can tell.
Using Sql2000: Select TableOne.Loc_CD as T1LocCD, TableOne.Loc_Name, TableTwo.A_Number, TableTwo.Loc_CD as T2LocCD >From TableOne FULL OUTER JOIN TableTwo On TableOne.Loc_CD = TableTwo.LocCD If it's absolutely necessary to get TableOne.Loc_CD and TableTwo.Loc_CD into the same column, use a union as such *not tested*: Select TableOne.Loc_CD as LocCD, TableOne.Loc_Name, TableTwo.A_Number, TableTwo.Loc_CD as T2LocCD >From TableOne Left OUTER JOIN TableTwo On TableOne.Loc_CD = TableTwo.LocCD Union Select TableTwo.Loc_CD as T1LocCD, TableOne.Loc_Name, TableTwo.A_Number, TableTwo.Loc_CD as T2LocCD >From TableTwo FULL OUTER JOIN TableOne On TableTwo.Loc_CD = TableOne.LocCD Matthew Small Web Developer American City Business Journals 704-973-1045 [EMAIL PROTECTED] -----Original Message----- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 2:24 PM To: CF-Talk Subject: LEFT AND RIGHT OUTER JOIN? I think I have solidly walked into the realm of "why are you trying to do this?" So any suggestions on alternate ways to look at this problem are welcome. Say I have two tables something like these: TABLE ONE Loc_CD Loc_Name ABC This DEF That DFE The Other TABLE TWO Loc_CD A_Number DEF 123 ALT 456 I need a combined table that would look like this. Loc_CD Loc_Name A_Number ABC This DEF That 123 DFE The Other ALT 456 Please presume I did not create the base table and have little control over their structure and data. But I need to somehow derive the combined table. How would one do this, is there such a thing as a double/left&right join? -------------- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:217157 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
