Exists and notExists Join in Microsoft Dynamics AX 2009 and Microsoft SQL Server

Some times while creating the query to have set the criteria that select this record from table A if  the reference of table A is available in table b or not.

This criteria can be easily achieved by using the exists and not exists join.

Below is the query that is showing the Exit join in X++;

smmBusRelTable smmBusRelTable;
smmBusRelFarmActivity smmBusRelFarmActivity;

Select count(recid) from smmBusRelTable
        exists join smmBusRelFarmActivity
    where smmBusRelTable.BRMCreditlineStatus == BRMCreditlineStatus::Preapproved
    &&
    smmBusRelFarmActivity.BusRelAccount == smmBusRelTable.BusRelAccount
        && (smmBusRelFarmActivity.CodeSecondary == "Dairy sharemilker"
        ||  smmBusRelFarmActivity.CodeSecondary == "Contract milker"
        ||  smmBusRelFarmActivity.CodeSecondary == "Personal use"
        ||  smmBusRelFarmActivity.CodeSecondary == "Retired"
        ||  smmBusRelFarmActivity.CodeSecondary == "Lifestyle"
        || (smmBusRelFarmActivity.CodePrimary == "Contractor" && smmBusRelFarmActivity.IsPrimary == NoYes::Yes));

    info(strfmt('%1', smmBusRelTable.RecId));

The above query is fetching the records from table smmBusRelTable but based on the criteria that their reference should be available in smmBusRelFarmActivity table and the range that we have applied on that table.

Same thing we can achieve for the "not exist" join like if i want to fetch the records from table A based on the criteria that its reference record should not be exist in the table B.

Below is the query for the not exists join.

smmBusRelTable smmBusRelTable;
smmBusRelFarmActivity smmBusRelFarmActivity;

Select count(recid) from smmBusRelTable

    where smmBusRelTable.BRMCreditlineStatus == BRMCreditlineStatus::Preapproved

       NotExists join smmBusRelFarmActivity

    where smmBusRelTable.BusRelAccount == smmBusRelFarmActivity.BusRelAccount

        && (smmBusRelFarmActivity.CodeSecondary == "Dairy sharemilker"

        ||  smmBusRelFarmActivity.CodeSecondary == "Contract milker"

        ||  smmBusRelFarmActivity.CodeSecondary == "Personal use"

        ||  smmBusRelFarmActivity.CodeSecondary == "Retired"

        ||  smmBusRelFarmActivity.CodeSecondary == "Lifestyle"

        || (smmBusRelFarmActivity.CodePrimary == "Contractor" && smmBusRelFarmActivity.IsPrimary == NoYes::Yes));

 info(strfmt('%1', smmBusRelTable.RecId));

note: for non exist join in the above AX query I am first applying the range on parent table i.e smmBusRelTable than doing the non exist join on the child table i.e smmBusRelFarmActivity. if we apply the range on the parent table after the join than it will not fetch the correct number of records.

Previously i was applying the range on the parent table after the join and it was not showing the same result as from sql server query and when i apply the range first on parent table and than join than it shows the same results as compare to the sql server query.

If we want to run the same query in Sql Server we can also do this.

Below is the example for using the exists and not exits joins in Microsoft Sql Server.

Not Exit Join:

select count(SMMBUSRELTABLE.RECID) from SMMBUSRELTABLE

where  Not Exists (select * from SMMBUSRELFARMACTIVITY

where SMMBUSRELFARMACTIVITY.BUSRELACCOUNT = SMMBUSRELTABLE.BUSRELACCOUNT

        and (SMMBUSRELFARMACTIVITY.CodeSecondary = 'Dairy sharemilker'

        or  SMMBUSRELFARMACTIVITY.CodeSecondary = 'Contract milker'

        or  SMMBUSRELFARMACTIVITY.CodeSecondary = 'Personal use'

        or  SMMBUSRELFARMACTIVITY.CodeSecondary = 'Retired'

        or  SMMBUSRELFARMACTIVITY.CodeSecondary = 'Lifestyle'

        or (SMMBUSRELFARMACTIVITY.CodePrimary = 'Contractor' and SMMBUSRELFARMACTIVITY.IsPrimary = '1'))

)

and

SMMBUSRELTABLE.BRMCreditlineStatus = 1

Exist Join:

select count(SMMBUSRELTABLE.RECID) from SMMBUSRELTABLE

where   Exists (select * from SMMBUSRELFARMACTIVITY

where SMMBUSRELFARMACTIVITY.BUSRELACCOUNT = SMMBUSRELTABLE.BUSRELACCOUNT

        and (SMMBUSRELFARMACTIVITY.CodeSecondary = 'Dairy sharemilker'

        or  SMMBUSRELFARMACTIVITY.CodeSecondary = 'Contract milker'

        or  SMMBUSRELFARMACTIVITY.CodeSecondary = 'Personal use'

        or  SMMBUSRELFARMACTIVITY.CodeSecondary = 'Retired'

        or  SMMBUSRELFARMACTIVITY.CodeSecondary = 'Lifestyle'

        or (SMMBUSRELFARMACTIVITY.CodePrimary = 'Contractor' and SMMBUSRELFARMACTIVITY.IsPrimary = '1'))

)

and

SMMBUSRELTABLE.CRTBRMCreditlineStatus = 1

This was all related to the exists and notexists joins in Microsoft Dynamics AX 2009 and Microsoft SQL Server.

Feel fry to comment or email me if you have any questions on this.

Thanks

Muhammad Zahid.