LibreOffice Base SQL: JOINing Tables

Today, I will continue the discussion of SQL queries with queries that involve more than one table. The SQL in this post is based on the “TeamMembers” database. For instructions on setting up the database in LibreOffice and a short description of the database and tables, see the post at http://wp.me/pvwht-a4.

JOIN: The Table Linking Keyword

If you need to gather data from two tables, you might think you would need to run multiple SELECT commands, but you would have a difficult time linking or JOINing the two tables together.

The JOIN keyword allows us to do just that. With it we can tell the database how the two tables are related.

As an example, if we needed a list of all the team members and their skills, we could use the following query:

In this query, we get the first and last name from the TeamMembers tables and the title from the Skills table. The FROM database is TeamMembers, but we JOIN it to the Skills database. The ON phrase is the key to the JOIN. Here we define the connection between the two databases. The SkillID field in the TeamMembers database should equal the ID field in the Skills database. If you look back at the structure of the database tables, you will find that SkillID is a foreign key connected to the ID field in Skills. The result set of this query is a list of all the team members and their skill title.

But what if you are looking for a programmer for a new project. It is easily done by adding a WHERE clause that looks for the skill “Programmer.”

Notice that in many places we use the pattern of table.field. This is a good idea to avoid confusion. As we work out our complex example, it is not only a good idea, but will become necessary for the database to know which table you are referencing. This is because some of the tables have fields with the same name.

Working Through A Complex Query

When working on a more complex query, it’s good to work it out a little at a time. This is what we will do for the following query: List all the team members by project along with each member’s skill. This query will involve all four tables in our database.

The start point is the team members being matched up to the projects. The TeamMember2Project table is where this connection is made. However, this table is just numbers match the ID of a team member to a project ID, so we will need to JOIN the other tables to get actual names and project titles. So to start, we just query the Teammember2Project table.

Note that I used the table.field notation here and will throughout the building of this query. My reason is because many of the tables have fields with the same name. For example, all the tables have ID fields except for TeamMembers2Project. Also, both Projects and Skills have Title fields.

Now that we have our table that links the members to their projects, we can being to build in our details. First, we will JOIN the Projects table.

The link to the Projects table is created by tying the ProjectID field to the ID field in Projects. Instead of pulling the ProjectID into our results, we pull the Title field from Projects. The result is a list of projects and the team members IDs.

Our second JOIN does the same as the first, except it links the ID field from TeamMembers to the TeamMembersID field. The list of fields for the data set is changed to the members first and last name and the project title.

Adding the WHERE clause narrows it down to only projects that begin with ACME.

So we can use JOIN to combine multiple tables into one query, linking them through their foreign keys with ON. We can systematically work our way through a complex query to get the results we want. In our example, we combined all four of the tables in our database.