Which SqlJoinType should you use?

DRAG DROP

A company is implementing Business Central.

In the per-tenant extension, TableA Header and TableA Line are document tables, and TableB Header and TableB Line are document history tables.

The company requires that the resulting dataset of query objects contain the following records:

• All records from TableA Header even if no matching record value exists in the linked TableA Line

• Records from TableB Header where a match is found in the linked TableB Line field

You need to configure the linked data item to generate the required dataset.

Which SqlJoinType should you use? To answer, move the appropriate SqUoinTypes to the correct dataset requirements. You may use each SqlJoinType once, more than once, or not at all. You may need to move the split bar between panes or scroll to view content.

View Answer

Answer:

Explanation:

Include all records from TableA Header even if no matching record value exists in the linked TableA Line: LeftOuterJoin

Include only matched records from TableB Header: InnerJoin

In SQL and similarly in Business Central when defining table relationships in query objects, the type of join determines how records from one table are combined with records from another table. Here’s what each join type means in the context of the company’s requirements: LeftOuterJoin:

A LeftOuterJoin includes all records from the ‘left’ table (TableA Header) and the matched records from the ‘right’ table (TableA Line). If there is no match, the result is NULL on the side of the ‘right’ table. This is why it fits the requirement to include all records from TableA Header even if there is no matching record in TableA Line.

InnerJoin:

An InnerJoin includes records when there is at least one match in both tables. So, it will only include records from TableB Header where a corresponding match is found in TableB Line. If there is no match, the records from TableB Header will not appear in the result set. This aligns with the requirement to include only matched records from TableB Header.

By using these join types, the company can ensure that their dataset includes the appropriate records from the document tables and document history tables according to their specified requirements.