Inner join X++ (Overdoing)

Inner join X++

A Join is used to combine rows from two or more tables, based on a related column. An inner join returns records that have matching values in both tables from a cross join. These are the standard SQL definitions.

Lets see a simple example what a cross join is. A cross join is a cartesian product, that means every record from the parent table is joined with every other record from the child table.

For example, if we have two simple tables like below:

The cross join will combine every record from the T1 table with every record from the T2 table:

In X++, the inner join will retrieve all records from the cross join that fullfill the condition of the matched field (relation), in this case the field F (records in orange), supposing we joined these tables on that field condition.

We will take in consideration the tables below to see how this works:

The ATC_Header table has an index composed of the field PersonId (PK) and this field is also the relation between the ATC_Header and ATC_Line tables.

The result of inner join will be a “result table” composed of all fields from both tables, (if not specified in the select statement). If specified, the query will show only those fields. We have to keep in mind that the output of a query (table joins), composed from a certain number of tables, will be a “table“. Here i call that “table” a “result table”.

The records shown will be only those records that have matching value of PersonId. A simple job can look like this:

The output of the job ATC_Joins:

Suppose we have another table ATC_Item which contains information if an item is stocked or no. And lets add this table to the statement above to see better how to use the “result table”:

Then add this table to the statement:

We can think of the statement like this: The first join (between ATC_Header and ATC_Line) generates a “result table” which we can use to join with the remaining table, ATC_Item table. The first “result table” will be (as seen in the previous job above):

We can see that not all items in the “result table” are included in the ATC_Item table (the condition where is the field Item), so we can predict from here that the final result will exclude the lines with the items L02 and K0001 from the first “result table” above. If we run the job we indeed get:

I hope i shed some light on this topic and happy coding 🙂

Ergys