Hi All:
 
OK, second part.
 
What happens when nulls are present in columns being joined?  If there
are nulls in the connecting columns of tables being joined, the nulls 
will never join because nulls represent unknown or inapplicable values.
E.F. Codd lists joins on nulls, the so-called "maybe-joins," as among
the necessary features of the relational model.  Give it a try and see
what FP 2.x does with nulls.  Nulls are empty, or as yet undefined
field values.  In VFP 5, nulls actually have an ASCII value of 0.  The
CHR(0) becomes the null.  (Imagine what this will do in a control
string sent to PCL or PostScript on a LaserJet!)
 
When you join tables, Fox compares the data in the specified fields
and displays the results of the comparison as a table or cursor of
the qualifying rows.  The results show a row for each successful
join.  Data from any of the tables is duplicated as needed.
 
Join query results replicate data from qualifying rows as needed in
order to regularize the table display.  As with any query, the results
of a join query display as a table without altering the database
tables.  The join operation merely allows the system to manipulate 
data from multiple tables as if they were contained in a single table.
 
You needn't name the join column twice in the select list, or even
include it in the results at all, in order for the join to succeed.
However, you many need to qualify the name of a join column with its 
table name in the select list or in the join specification of the
WHERE clause.
 
As with any SELECT statement, the columns you name after the SELECT
keyword are the columns you want the query results to display, in
the order you designate.  If you use SELECT *  the columns appear in
their CREATE TABLE order.
 
In order to make join queries easier to type and more readable, you
may want to give the tables alias names in the table list.  Assigning
an alias to each table name is particularly helpful when you join on
identically named columns, such as I use, which have to be qualified
with the table name each time they're used.  Note the following:
 
   SELECT amount,    ;
          due_date,  ;
     FROM Account A, ;
          Invoice I  ;
    WHERE I.account = A.account ;
     INTO CURSOR AmtDue
 
A join is often based on equality, or matching values in the joining
columns.  Joins based on equality are indicated with the "=" comparison
operator in the join part of the WHERE clause.  Joins can also be con-
structed on other comparitor conditions, too.  The following work:
 
    >    greater than
    >=   greater than or equal to
    <    less than
    <=   less than or equal to
    !=   not equal to    (  or even # works, too )
    ==   exactly equal to  ( I don't quite understand this operator
         except with strings.  Numerical, date and logical fields
         seem to insist on = being the same as ==. )
 
In relational database parlance, joins that use any of the comparison
operators are collectively called "theta" joins.  Lay _that_ on your
fellow local developers and watch them look askance!  (Tell 'em you
learned it on FIDOnet...)
 
Well, out of time again.  But maybe by now you know where this is leading.
Don't touch that dial....
 
David in Dallas.
--- Maximus/2 3.01
---------------