4D v11 SQL includes functionality which allows developers to directly include SQL statements in their methods through the BEGIN SQL and END SQL commands. One of the SQL clauses not included in 4D is JOIN.

For information on the SQL clause JOIN check out its wikipedia entry:

https://en.wikipedia.org/wiki/Join_(SQL)

This clause is used in SQL to effectively create a relationship between two tables. Use of a JOIN in 4D can be especially useful because there does not have to be any pre-existing relationship between tables or fields to use the JOIN clause. Instead of using the JOIN clause a WHERE clause can be included in a SELECT statement from two tables. The WHERE clause can be used to simulate JOIN by setting fields from two separate tables equal to each other. This effectively simulates the behavior of a SQL JOIN statement.

The following code would get all records from the CUSTOMERS table and INVOICES table where the ID field in CUSTOMERS matches the CUSTID field in INVOICES. It effectively creates a relationship between the two tables. This example also queries for customers named Josh Fletcher, so only invoices from that customer will be returned:

BEGIN SQL
SELECT *
FROM CUSTOMERS, INVOICES
WHERE CUSTOMERS.ID = INVOICES.CUSTID
& CUSTOMERS.NAME = 'Josh Fletcher';
END SQL

For more sample code on creating SELECT statements with the WHERE clause used as to simulate a JOIN clause see the 4D v11 SQL online reference at the following link:

(function() { var didInit = false; function initMunchkin() { if(didInit === false) { didInit = true; Munchkin.init('531-MRT-468'); } } var s = document.createElement('script'); s.type = 'text/javascript'; s.async = true; s.src = '//munchkin.marketo.net/munchkin.js'; s.onreadystatechange = function() { if (this.readyState == 'complete' || this.readyState == 'loaded') { initMunchkin(); } }; s.onload = initMunchkin; document.getElementsByTagName('head')[0].appendChild(s); })();