Re: Simulating a natural join in ISQL

Kate Juliff <kcj@netcom.com> wrote: >I have two tables that I wish to join without losing any data. I've never lost data yet in a join. Be careful to specify SELECT instead of DELETE :). Actually, what you're talking about sounds like an OUTER join. >The first >has a number of rows, all with an order number and line number. This is >the primary key. create table first ( f_order INTEGER, f_line INTEGER, f_data ... ); >A second table has other information, but also has the >same primary key. However with this table not all order number line >numbers appear in it. create table second ( s_order INTEGER, s_line INTEGER, s_data ... ); >If I join with a select on equality of primary keys >I'll lose the rows that have order number line numbers in the first table >but not in the second. I need to get around this problem. Any suggestions? SELECT f_order, f_line, f_data, s_data FROM first, OUTER second WHERE s_order = f_order AND s_line = f_line This query will return f_order, f_line, and f_data for ALL rows in first. If there's a matching row in second, s_data will be included, otherwise NULL will be given for s_data. Hope this helps. -- Mark Rafn dagon@halcyon.com http://www.halcyon.com/dagon/ Decadence is its own reward.