Search This Blog

Thursday, March 28, 2013

Diffrent Type of Joins and Linked Type in AX 2009


Different type of Joins and Link Type

Car Table

CarId
ModelYear
CarBrand
Model
Mileage
ModelYear
CarId
CA101
2008
Mahindra
Scorpio
15
2008
CA101
CA102
2009
Suzuki
800
12
2009
CA102
CA103
2007
Hundai
i20
5
2007
CA103
CA104
2007
Toyato
Inova
3
2007
CA104
CA105
2009
BMW
 
5
2009
CA105
CA106
2010
Benz
AZ12
10
2010
CA106

 
Rental Table

RentalId
CustAccount
CarId
FromDate
ToDate
ToDate
FromDate
RE101
1104
CA101
10/12/2010
10/29/2010
10/29/2010
10/12/2010
RE102
1102
CA102
11/17/2010
12/14/2010
12/14/2010
11/17/2010
RE103
1203
CA103
11/24/2010
12/15/2010
12/15/2010
11/24/2010
RE104
1301
CA104
12/10/2010
1/5/2011
1/5/2011
12/10/2010
RE105
1304
CA101
1/3/2011
1/18/2011
1/18/2011
1/3/2011
RE108
1303
 
 
 
 
 
RE107
1202
 
 
 
 
 
RE106
2024
CA103
1/10/2011
1/29/2011
1/29/2011
1/10/2011

 
Inner Join

Select records from main table that have matching record form join table.

Main table record and join table records are joined together  and show as single record.

Code                                                                                                                                                    

static void JoinExamples(Args _args)                                                                                                      

{                                                                                                             

    CarTable carTable;                                                                                                      

    RentalTable rentalTable;                                                                                                          

    ;                                                                                                          

    while select carTable join rentalTable                                                                                                

        order by carTable.CarId                                                                                                        

        where carTable.CarId == rentalTable.CarId                                                                                                 

                                                                                                               

        {                                                                                                     

            info(strfmt("Car id is %1 renatla id is %2",carTable.CarId, rentalTable.RentalId));                                                                                                   

        }                                                                                                     

}

               
Output                                                                                


Outer Join                                                                          

                                                                               

Select all the records from main table and related records from join table                                                                                                                                            

If there is no match, the field from join table is empty                                                                   

Code

while select carTable outer  join rentalTable                                                                                                       

        order by carTable.CarId                                                                                                        

        where carTable.CarId == rentalTable.CarId 

Output



Exists Join                                                                                                                                                                                          

Selecting record from main table only if there is matching record in join table.                                   

It shoul be return only main table record, not a related table record.                                                                      

Code

while select carTable Exists  join rentalTable                                                                                                       

        order by carTable.CarId                                                                                                        

        where carTable.CarId == rentalTable.CarId 

Output



 

Not Exists Join                                                                                                                                                                 

Selecting record from main table only if there is not matching record in join table.                           

It is directly irrelevant to the Exists join.                

Output                

           

 
Link Type:  Delayed

A pause is inserted before linked child data sources are updated. This enables faster navigation in the parent data source because the records from child data sources are not updated immediately.

Form Output



Form Design in Data Source Level:



 

Link Type: Active

The child data source is updated immediately when a new record in the parent data source is selected. Continuous updates consume lots of resources.

Link Type: Passive

Linked child data sources are not updated automatically. Updates of the child data source must be programmed on the active method of the master data source.

1 comment: