Re: Simulating a natural join in ISQL

kcj@netcom.com (Kate Juliff) wrote: > >I have two tables that I wish to join without losing any data. The first >has a number of rows, all with an order number and line number. This is >the primary key. 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. 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? > > Kate, You need to use the "great" SQL feature of the OUTER JOIN. The outer join allows you to join two tables and get ALL the rows even when the join criteria in one table does not exist in another. So, for your example, you would issue a statement something like this : SELECT ..... FROM first_table, OUTER second_table WHERE first_table.order_nbr = second_table.order_nbr AND first_table.line_nbr = second_table.line_nbr You might like to take a look at Appendex L "Outer Joins" in your Informix SQL Reference manual. Good luck. --------------------------------------------------------------------- * Rick Clark ( President & CEO ) Internet: rick@imagepro.com * * Imaging Business Consultants, Inc. AOL: rickjclark@aol.com * * San Jose, California 95124, USA. * * Phone: 408 369-9220 * * Fax: 408 369-9223 * ---------------------------------------------------------------------