Message-Id: <10709.124510@fatcity.com> From: Adam Turner Date: Wed, 13 Dec 2000 17:43:41 -0500 Subject: RE: left outer to two tables ...??? Thanks for the lesson. It does make sense. My syntax was off. I am used to SQL Server where it would be table a left outer join table b on a.id = b.id (or whatever field you need) the (+) syntax was making me crazy. I now understand it thanks to the several responses I got. I couldn't tell from the SQL reference online that I 1. did not need to include outer_join somewhere 2. that the (+) was it not + or something. thanks again adam -----Original Message----- From: Dara Vaughn [mailto:dara.vaughn@wcom.com] Sent: Wednesday, December 13, 2000 11:41 AM To: Multiple recipients of list ORACLE-L Subject: Re: left outer to two tables ...??? Adam, The problem that you are probably wrestling with is that you cannot have the same table outerjoined to two DIFFERENT tables as in your example. BTW, this looks like access syntax, not Oracle syntax. I will reply in Oracle syntax. > > table A left outer join table B > > table A left outer join table C In Oracle, you can do the following: WHERE B.column_name = A.column_name (+) and C.column_name = A.column_name (+) --This is invalid since A is already outerjoined to B In this example, ALL rows where A.column_name = B.column_name are returned, as well as ALL values of B.column_name even if there are no matching values of A.column_name. Where there are no matching values of A.column_name, any column selected from table A will return a null (as there are no matching values.) It's easy to remember, the side that you put the (+) on in the where clause is the table that will return the null values. In the example above, since you have already outer-joined table table A to table B, you cannot outer-join table A again to table C. Think about it... You would be saying include ALL values of table B.column_name that don't have matches in A.column_name AND ALL values of table C.column_name that don't have matches in B.column_name. You can chain outer-joins (e.g. B.column_name = A.column_name (+) and A.column_name = C.column_name (+) ), but you can't have the same table (A) outer-joined to multiple tables (B & C). Dara Vaughn Oracle DBA Quality Software Engineering ----- Original Message ----- To: Multiple recipients of list ORACLE-L Sent: Wednesday, December 13, 2000 9:46 AM > You have to joint columns within the table. E.g > select * from table A a, table B b > where a.some_colums (+)=b.some_column; > > If there is not a one to one relationship with some_column in table A and > table B then then the table having a many to one relationship goes first in > the equation with the (+) symbol next to it. In the example above table A > has a many to one relationship on some_column with table B. > > HTH, > Ruth > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" > Sent: Tuesday, December 12, 2000 4:21 PM > > > > quick dumb help... > > > > can someone please send me some outer join syntax? I am having trouble > > getting > > > > table A left outer join table B > > table A left outer join table C > > > > I am having some trouble getting my hands around this. > > > > thanks > > > > adam > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Adam Turner > > INET: ATurner@concreteinc.com > > > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California -- Public Internet access / Mailing Lists > > -------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Ruth Gramolini > INET: rgramolini@tax.state.vt.us > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dara Vaughn INET: dara.vaughn@wcom.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may