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:

SELECT “FirstName”, “LastName”, “Title”
FROM “TeamMembers” JOIN “Skills”
ON “TeamMembers”.”SkillID” = “Skills”.”ID”;

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.”

SELECT “FirstName”, “LastName”, “Title”
FROM “TeamMembers” JOIN “Skills”
ON “TeamMembers”.”SkillID” = “Skills”.”ID”
WHERE “Skills”.”Title” = ‘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.

SELECT “TeamMember2Project”.”TeamMemberID”, “TeamMember2Project”.”ProjectID”
FROM “TeamMember2Project”;

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.

SELECT “TeamMember2Project”.”TeamMemberID”, “Projects”.”Title”
FROM “TeamMember2Project” JOIN “Projects”
ON “TeamMember2Project”.”ProjectID” = “Projects”.”ID”;

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.

Now, let’s grab our team members.

SELECT “TeamMembers”.”FirstName”, “TeamMembers”.”LastName”, “Projects”.”Title”
FROM “TeamMember2Project” JOIN “Projects”
ON “TeamMember2Project”.”ProjectID” = “Projects”.”ID”
JOIN “TeamMembers”
ON “TeamMember2Project”.”TeamMemberID” = “TeamMembers”.”ID”;

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.

Now, we need to pull in the skills.

SELECT “TeamMembers”.”FirstName”, “TeamMembers”.”LastName”, “Skills”.”Title”, “Projects”.”Title”
FROM “TeamMember2Project” JOIN “Projects”
ON “TeamMember2Project”.”ProjectID” = “Projects”.”ID”
JOIN “TeamMembers”
ON “TeamMember2Project”.”TeamMemberID” = “TeamMembers”.”ID”
JOIN “Skills”
ON “TeamMembers”.”SkillID” = “Skills”.”ID”;

Our final JOIN brings in the Skills table. We link its ID to the SkillID field of the TeamMembers database. This gives us all of our data, but it is a bit disorganized. Let’s give it some order.

SELECT “TeamMembers”.”FirstName”, “TeamMembers”.”LastName”, “Skills”.”Title”, “Projects”.”Title”
FROM “TeamMember2Project” JOIN “Projects”
ON “TeamMember2Project”.”ProjectID” = “Projects”.”ID”
JOIN “TeamMembers”
ON “TeamMember2Project”.”TeamMemberID” = “TeamMembers”.”ID”
JOIN “Skills”
ON “TeamMembers”.”SkillID” = “Skills”.”ID”
ORDER BY “Projects”.”Title”, “TeamMembers”.”LastName”;

Our ORDER BY sorts the list by project then by last name of the team members. Let’s go one step further. What if we only wanted a list of the ACME projects?

SELECT “TeamMembers”.”FirstName”, “TeamMembers”.”LastName”, “Skills”.”Title”, “Projects”.”Title”
FROM “TeamMember2Project” JOIN “Projects”
ON “TeamMember2Project”.”ProjectID” = “Projects”.”ID”
JOIN “TeamMembers”
ON “TeamMember2Project”.”TeamMemberID” = “TeamMembers”.”ID”
JOIN “Skills”
ON “TeamMembers”.”SkillID” = “Skills”.”ID”
WHERE “Projects”.”Title” LIKE ‘ACME%’
ORDER BY “Projects”.”Title”, “TeamMembers”.”LastName”;

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.

Next, we will look at using aggregation functions in our queries.

Written with StackEdit.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s