Select Joins

Hossein.K

6,642

Like ( 0 )

Report

Use joins to link tables so they can return values together. By default, inner joins
are created. The following table describes the types of joins available:

Join Type	Use to
inner	Combine records into one table only when there are matching values in a common field.
outer	Combine records into one table even if there are no matching values in the common field.
exists	Combine records from one table whenever a value exists in a common field in another table.
notexists	Combine records from one table whenever a value in a common field in another table does not exist.

Not only is there additional syntax needed to specify what table is being joined,
but the where clause also has to be modified. Within the where clause, the two
fields that relate the tables must be specified in addition to any other conditions
of the search. When using an inner or outer join the table buffers of both tables
are populated with the retrieved records. An exists or not exists join only
populates the first table.Example: Using a Join
The join shown in the following code combines records from the Customer table
and the Customer transaction table to show all vouchers and dates for customer
4000.

CustTable custTable;
CustTrans custTrans;
while select AccountNum from custTable
join custTrans
where (custTrans.AccountNum ==
custTrans.AccountNum)
&& custTrans.AccountNum =="1103"
{
   print custTable.accountNum, " 
   ",custTrans.Voucher,' ',
 date2str(custTrans.TransDate,213,1,4,2,4,2);
}
pause;