Find 1 of the 2 missing records

Question

The InventItemLocation table should contain 2 records, 1 with InventDimId 'A' and 1 with InventDimId 'B'.

I am trying to search the items where InventDimId 'A' is missing.

The problem with the following code, is that it also shows items which do contain InventDimId 'A' (MissingInventDimId) because it is checking the line.

static void FindMissingInventItemLocation(Args _args)
{
    InventTable inventTable;
    InventItemLocation inventItemLocation;
    str info;

    while select inventItemLocation
    notexists join inventTable
    where inventTable.ItemId == inventItemLocation.ItemId &&
    inventItemLocation.inventDimId == 'MissingInventDimId' &&
    inventItemLocation.inventDimId == 'ExistingInventDimId'
    {
        info (inventItemLocation.ItemId);
    }

}

Answer 1

If you are trying to find records that have ExistingInventDimId, but do not have MissingInventDimId then try the following

static void FindMissingInventItemLocation(Args _args)
{
    InventItemLocation  inventItemLocationExisting;
    InventItemLocation  inventItemLocationMissing;
    str                 info;
    ;

    while select inventItemLocationExisting
        where inventItemLocationExisting.inventDimId == 'ExistingInventDimId'
    notexists join inventItemLocationMissing
        where inventItemLocationMissing.ItemId == inventItemLocationExisting.ItemId &&
              inventItemLocationMissing.inventDimId == 'MissingInventDimId'
    {
        info (inventItemLocationExisting.ItemId);
    }
}