Re: Simulating a natural join in ISQL
----- Begin Included Message -----
From ilist@rmy.emory.edu Fri Jul 7 06:08:35 1995
From: kcj@netcom.com (Kate Juliff)
Subject: Simulating a natural join in ISQL
Date: Fri, 7 Jul 1995 03:07:18 GMT
To: informix-list@rmy.emory.edu
X-Informix-List-To: rt10hp@csl.gov.uk
X-Informix-List-Id: <news.15212>
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?
----- End Included Message -----
Kate,
Try an outer join:
select col1, col2, ... from tab1, OUTER tab2 where tab1.col1 = tab2.col2
This selects all rows from tab1 with the corresponding data from
tab2, AND returns null values for tab2 columns where the appropriate
row does not exist. You can use this in sql statements and ACE reports.
However, I only know about ISQL v4.00 running on SE, so it may be
different if you've got a better system!
Hope this helps.
Cheers,
Richard
-----------------------------------------------------------------
| _ /\\ | Richard Thomas |
| \\ 0_ | r.thomas@csl.gov.uk |
| \\ / | |
| oo/ | "20 Regal and a four-pack, |
| / \\ | I guess I'm set for the night" |
| \\_/ | - "TV Tan" The Wildhearts |
-----------------------------------------------------------------