Left joins in Priority

I landed a new consultancy gig yesterday, helping improve a programmer who is currently at intermediate level. It's much easier working with someone who already knows a fair amount of Priority programming as opposed to working with someone who is new to the subject. During the course of yesterday's session, we came across a subject which is slightly complicated, left joins. I feel that I didn't explain it very well yesterday, but this morning, when walking the dog, I found a much better way of explaining.

The standard definition of a left join is the LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match. The Priority programmer's bible (aka the SDK) calls this an outer join and explains it thus: An outer join is represented in Priority’s syntax by a question mark (?) following the table ID:

SELECT ... FROM FNCITEMS, FNCITEMSB ? 
WHERE FNCITEMSB.FNCTRANS = FNCITEMS.FNCTRANS 
AND FNCITEMSB.KLINE = FNCITEMS.KLINE;

As opposed to regular joins, an outer join preserves unmatched rows. Thus, if there is no join record, a null record will be retrieved from the join table and the query will succeed. Is that any clearer?

I explained it by saying that when we need a join to a second table but we are not sure that there is a matching record in the second table, then use the question mark. Here is an example: in one of my company's divisions, use is made of the status of an order line, whereas in other divisions, the order lines do not have a status. As the order line status is not stored in the order lines table but in a secondary table, we cannot be sure whether there is a matching record in the secondary table and so every join to this table should use a question mark. The above syntax is fine in an SQLI step but isn't suitable when programming a screen, and of course yesterday we were programming a screen.

All the above is about as clear as mud. The explanation which I hit upon this morning requires the use of two concepts which Priority skirts around: primary keys and foreign keys. In Priority-speak, a primary key is normally the auto-unique key of a table, but in some cases it can be two fields taken together (this of course is what we had yesterday evening). Priority doesn't have the concept of a foreign key which is defined in standard SQL as a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. For example, an order line has a field PART which refers to the primary key (part) in the parts table; from the point of view of the order lines table, PART is a foreign key.

What does this have to do with left joins? I'm glad you asked. My insight this morning was: whenever we join two tables, there is no need for the question mark (left join) if we are joining between a foreign key from the 'home' table (e.g. order lines) to the primary key of the joined table (e.g. ORDERITEMS.PART = PART.PART). If ORDERITEMS.PART has 'no' value (i.e. its value is 0), it will match the zero record in table PART and so everything is ok. But if we are doing the reverse - in which we join via the primary key of the 'home' table to a field in the joined table (normally this field will be part of the joined table's primary key) - we have to use the question mark (aka left join) syntax as there is not necessarily a record in the joined table which matches the primary key of the 'home' table.

Here's an example: the PART table contains many fields, some of which are foreign keys and some hold values of the given part. There is a table called PARTEXTFILE, which contains the names of external files connected to the part; in other words, there exists a 1 to many relationship between PART and PARTEXTFILE.  The primary key of PARTEXTFILE is the part number and a counter for that part; if that counter is -1, then the external file is a picture of the given part. There may be no picture for a given part, so we need to use a left join when using PARTEXTFILE; put another way, we are joining on the basis of the 'home' table's primary key to a field in the joined table, which means that we have to use the question mark.

So why not use the question mark in every join, I was asked. Using the question mark where it is not called for will join many records which should not be joined otherwise. I'll sum this up with a little table to make things as clear as I possibly can:

Home table	Joined table	Join
Foreign key	Primary key	regular
Primary key	[Primary key]	LEFT