Not using an outer join. You can only do a single outer join in one statement.
There might be a way to do it using 3 select statements and 2 unions to get it all in one command, but it would not be easy. It would look something like this: create OneView as Select t1.t1_id, t1.t1_select, t2.t2_id, t3.t3_id from table1 t1, table2 t2, table3 t3 where t1.t1_id = t2.t2_id and t1.t1_id = t3.t3_id Union Select t1.t1_id, t1.t1_select, t2.t2_id, ' ' from table1 t1 where t1_id = t2_id and t1_id not in (select t3_id from table3) Union Select t1.t1_id, t1.t1_select, ' ', T3_t3_id from table1 t1, table2 t3 where t1.t1_id = t3.t3_id ............. This is not quite complete. You would need to join all 3 tables, and then pick up all of the other rows where table2 didn't have anything, and then where table3 didn't have anything and then where neither table2 and table3 did not have a match Troy Troy -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fogelson, Steve Sent: Monday, July 12, 2004 7:07 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Join ? Troy, Thanks for the response. Is there a way to combine these together? Steve -----Original Message----- From: Troy Sosamon [mailto:[EMAIL PROTECTED] Sent: Sunday, July 11, 2004 6:17 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Join ? You are looking for the same thing Larry was. You need to use 2 view doing left outer joins. Create view1 as Select t1.t1_id, t1.t1_select, t2.t2_id from table1 t1 left outer join table2 t2 on t1.t1_id = t2.t2_id create view2 as Select v1.t1_id, v1.t1_select, v2.t2_id, t3.t3_id from view1 v1 left outer join table3 on v1.t1_id = t3.t3_id view2 should have what you are looking for. Troy -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fogelson, Steve Sent: Sunday, July 11, 2004 2:09 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Join ? Witango 5.0, R:Tango 5.0, R:Base 6.5++ I have 3 tables as follows: table 1 t1_id1 PK t1_id2 t1_select table 2 t2_id PK t1_id1 FK t2_start t2_end table 3 t3_id PK t1_id2 FK t3_start t3_end I want to end up with a view with the following: t1.t1_id t1.t1_select t2.t2_id (null if no matching key in table2) t3.t3_id (null if no matching key in table3) I want to select all records from table 1 where t1_select=xxx even if there isn't a foreign key match in table 2 and/or table 3, but if there is, I would like to include t2.t2_id and/or t3.t3_id in the results. Matches from table 2 and table 3 depend on todays date >= t2_start and <= t2_end and like wise on table 3. I don't think a join will work as it will not include rows from table 1 if no matching keys from table 2 and table 3. Is this possible? Thanks Steve Fogelson Internet Commerce Solutions
