Delphi and Access walkthrough
Using SQL to join the tables
So far we have been able to use Delphi to look at the contents of a single table in our Access database. That's not enough however, we need to be able to get data from more than one table.
We can ask the Query object that we created in the last section to do this for us. We give the query object instructions by using a database command language called SQL (Standard Query Language). It is indeed a standard, you'll find that pretty much every database in the world responds to SQL commands - although sometimes there are small differences in exactly how they like it to be formatted.
We can ask the Query object that we created in the last section to do this for us. We give the query object instructions by using a database command language called SQL (Standard Query Language). It is indeed a standard, you'll find that pretty much every database in the world responds to SQL commands - although sometimes there are small differences in exactly how they like it to be formatted.
|
Select the FDQuery1 object and just below the list of properties, find the link to the 'Query Editor'
Here's the command to type into the query editor. |
SELECT PUPILS.ID, PUPILS.USERNAME, PUPILS.USERSCORE, SURNAMES.SURNAME
FROM PUPILS
INNER JOIN SURNAMES
ON (PUPILS.USERNAME = SURNAMES.FIRSTNAME)
The first line tells it what fields we want to retrieve from the database. To avoid any ambiguity we specify the fields in two parts, the table name first, then a full stop, then the field name.
(tip: you can replace this list of field names with a * and it will retrieve everything.
The second and third lines tell it which tables we want to use - specifically that these tables are joined on a specific set of fields. In this case the join is between the PUPILS.USERNAME and SURNAMES.FIRSTNAME fields. That is the function of the last line, which explains how the tables are related. The brackets around the expression are needed for Access but you may not find they are needed in other SQL versions.
(tip: you can replace this list of field names with a * and it will retrieve everything.
The second and third lines tell it which tables we want to use - specifically that these tables are joined on a specific set of fields. In this case the join is between the PUPILS.USERNAME and SURNAMES.FIRSTNAME fields. That is the function of the last line, which explains how the tables are related. The brackets around the expression are needed for Access but you may not find they are needed in other SQL versions.
Once the join has been made, you'll find you have access to fields in both tables - as if they were a single table. You can test this by finding and dragging a TDBEdit field from the toolbox. Set the datasource to Datasource1 and click on the Datafield property. You have access to all the fields listed in your SQL statement.
