Description:-
If you would like to retrieve data from several tables or at least use ranges from different tables in the select statement, you should use one of the join parameters listed in the following sections.
CarTable
The
following list shows the test data for the CarTable:
CarId
|
ModelYear
|
CarBrand
|
Model
|
Mileage
|
1
|
2007
|
BMW
|
320
|
2299
|
2
|
2007
|
Mercedes
|
C220
|
2883
|
3
|
2008
|
Toyota
|
Corolla
|
4032
|
4
|
2006
|
Volkswagen
|
Golf
|
49902
|
5
|
2002
|
Jeep
|
Grand Cherokee
|
65662
|
6
|
2003
|
BMW
|
Z3
|
11120
|
7
|
2000
|
Volkswagen
|
Golf
|
76322
|
RentalTable
The following list shows the test data for the RentalTable:
The following list shows the test data for the RentalTable:
RentalId
|
CustAccount
|
FromDate
|
ToDate
|
CarId
|
1
|
1101
|
24.03.2009
|
25.03.2009
|
1
|
2
|
1103
|
23.03.2009
|
25.03.2009
|
3
|
3
|
1103
|
02.05.2009
|
11.05.2009
|
1
|
4
|
1102
|
10.05.2009
|
17.05.2009
|
5
|
5
|
1104
|
10.12.2009
|
20.12.2009
|
6
|
CustTable
The following list shows the test data for the CustTable:
The following list shows the test data for the CustTable:
AccountNum
|
Name
|
CustGroup
|
Blocked
|
1101
|
Forest Wholesales
|
10
|
No
|
1102
|
Sunset Wholesales
|
20
|
No
|
1103
|
Cave Wholesales
|
10
|
No
|
1104
|
Desert Wholesales
|
30
|
Yes
|
Inner join
The inner join is the most common
join as it joins two tables that are linked together typically by a one-to-many
relationship.
The first table used in the select statement, should
be the "many" part of the relationship, so in our example, we can say
that a record from the CarTablecan exist many times in
the RentalTablemaking the RentalTablebeing used
first.
As you might notice, the sorting in a joined select is
done first with the innermost table; in this case, the CarTable. When no
sorting has been specified, AX uses the primary index set on the table. In this
case, it uses the CardIdxindex on the CarTable:
static void selectInnerJoin(Args
_args)
{
CarTable carTable;
RentalTable rentalTable;
while select rentalTable
join carTable// same as writing inner join
where carTable.CarId
== rentalTable.CarId
{
info(strfmt("RentalId
%1 is a %2 %3", rentalTable.RentalId, carTable.CarBrand,
carTable.Model));
}
}
Outer join
Outer join
An outer
join is used to join two tables but to also include the records
that do not have a corresponding match in the joined table. In the following
example, you will see that all records in the CarTableare
selected even though some of the cars have never been rented:
static void selectOuterJoin(Args
_args)
{
CarTable carTable;
RentalTable rentalTable;
while select carTable
outerjoin rentalTable
where rentalTable.CarId
== carTable.CarId
{
if
(!rentalTable.RecId)
info(strfmt("No
rentals for the car with carId %1", carTable.CarId));
else
info(strfmt("RentalId
%1 is a %2 %3", rentalTable.RentalId,
carTable.CarBrand, carTable.Model));
}
}
Exists join
Exists join
The existsjoin
does pretty much the same as the inner join except for
one important thing; it does not fetch the records from the joined table. This
means that the rentalTablevariable
cannot be used within the while loop in the
following example as it will never have any data:
static void selectExistsJoin(Args
_args)
{
{
CarTable carTable;
RentalTable rentalTable;
while select carTable
existsjoin rentalTable
where rentalTable.CarId
== carTable.CarId
{
info(strfmt("CarId
%1 has a matching record in rentalTable",CarTable.CarId));
}
}
NotExists join
Obviously, the notexistsjoin is the opposite of the existsjoin in terms of how it works. This means that it will return all records from the main table where there does not exist a record in the joined table as described by the where clause. This means that the following example will produce the opposite result from the previous example:
NotExists join
Obviously, the notexistsjoin is the opposite of the existsjoin in terms of how it works. This means that it will return all records from the main table where there does not exist a record in the joined table as described by the where clause. This means that the following example will produce the opposite result from the previous example:
static void selectNotExistsJoin(Args
_args)
{
CarTable carTable;
RentalTable rentalTable;
while select carTable
notexistsjoin rentalTable
where rentalTable.CarId
== carTable.CarId
{
info(strfmt("CarId
%1 does not has a matching record in rentalTable", CarTable.CarId));
}
}
Thanks for comments.....