Advanced Database – Apply, modify a subtract join
For this example we need to add a record to the ‘Car and owner details’ table with a Car Reg Number that doesn’t appear in the ‘Jobs’ table.
Open the Car and owner details table and add a new record at the bottom of the table. Don’t add anything to the jobs table.
Close the table and reopen the query we used in the last example.
A subtract join is the opposite of an outer join, i.e. it includes only results in one table that don’t match any records in the other.
Base doesn’t have a direct option in design mode to do this, so it’s best to use some simple SQL.
To toggle into SQL editing mode, click the ‘Switch Design View On/Off’ icon.
Delete any text that is already in the SQL editor.
Then enter the text exactly as it appears in the image.
This SQL code sets up a left outer join between Car and owner details table and Jobs table, listing all the fields for any records that have a Car Reg Number in ‘Car and owner details’, that does not appear in the ‘Jobs’ table (IS NULL).
Press F5 to run the query
Notice that the query only returns the one record that is in the Car and owner details table, but not in the jobs table.
To see how this can be created without SQL code, click the Switch Design View On/Off icon
Don’t worry too much about the SQL commands for now, we’ll discuss these in more detail later on in the course.
Right click the connecting line and select edit to open join properties.
The SQL code we entered has changed the join type to a Left join. It can be modified here.
Notice in the query table, it has set the Criterion to ‘IS EMPTY’ which was the ‘IS NULL’ part of our SQL code.
This is a valid way of creating the same query without SQL.
