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
