Lesson 3-3

Filtering the Balance table with JOIN

Our next option for filtering the checking balance query is using a JOIN. But before we can start using it we need to learn how does it work.

JOIN basics

Let’s start with a JOIN basic understanding. Say, we added a new attribute in the the Warehouses catalog:

and filled it out with some string values:

Now we want this new information (addresses) to be shown in our Balance of material totals dynamic list:

How we do this, given that the Balance table stores only a reference to the Warehouse catalog?

OK, here is a plan:

We could cycle through the Balance table records and look for a correspondent warehouse for each of them, using the warehouse’s unique number as the search condition. Then we could read the address of a warehouse we found and put it into an additional column of a resulting recordset.

Well, this is exactly what JOIN does. Here is a basic JOIN syntax:

SELECT <fields list>
FROM <Table1>
[LEFT | RIGHT] [INNER | OUTER] JOIN <Table2> ON <join condition>
WHERE <filter conditions>

To get the Balance table with warehouses’ addresses we could use the following query:

SELECT
	Balance.Material,
	Balance.Warehouse,
	Balance.QuantityBalance,
	Warehouses.Address
FROM
	AccumulationRegister.BalanceOfMaterials.Balance AS Balance
		LEFT JOIN Catalog.Warehouses AS Warehouses
		ON Balance.Warehouse = Warehouses.Ref

Let’s try it.

This was the simplest example of JOIN work - every record of Table1 (the Balance table in our case) had exactly one matching record in the Table2 (Warehouses catalog). But what happens if there is no match in Table2? Say, we delete the Additional warehouse. What we get in the resulting recordset in this case?

Well, it depends on what type of JOIN we use - INNER or OUTER.

INNER and OUTER JOINs difference

The difference between these two types of JOINs is all about the query behaviour in the case when some records in Table1 have no matches in Table2.

OUTER JOIN will include these records in the resulting recordset and set to NULL all fields taken from Table2:

INNER JOIN will exclude (i.e. filter out) these records from the recordset:

Let’s see it on practice.

Note, that when we restored the additional warehouse and selected it in the broken GoodsReceipt, we got the same five records that we had with OUTER JOIN. So INNER and OUTER JOINs give the same resulting recordset when every Table1 record has exactly one matching Table2 record (this is usually called “one-to-one relation”). So there is a difference between INNER and OUTER JOINs for the one-to-none relation, but not for the one-to-one relation.

What about one-to-many relation, i.e. the situation when there are more than one records in Table2 that match one record in Table1? In this case every record of Table1 will be repeated in the resulting recordset as many times as many matching records are in the Table2.

 Lesson 3-2 | Course description | Lesson 3-4