JOIN Query

So far, we have learned how to get data out of one table and a time. However, further understand SQL and get useful information, we need combine tables and ask more complex questions. Consider the following question:

Give me the names and addresses of all the members who are enrolled in the Aerobics program.

How do we get the information being asked with the following issues?

• The GymMembers table contains member's names and addresses but no information about services.
• The GymService table simply contains service names and cost
• The member service table contains the required information - who is enrolled in what but only IDs and no names.

To get this information using SQL, we will ‘JOIN’ the three concerned tables together. JOIN can be confusing because the actual JOIN clause is not really necessary while writing JOIN queries. However, once we are familiar with the fundamentals of combining tables, using JOIN becomes easier and intuitive. First, let us look at the query that will answer the above question:

SELECT GymMembers.MemberName, GymMembers.MemberAddress
FROM MemberService, GymService, GymMembers
WHERE MemberService.MemberID = GymMembers.MemberID
AND GymService.ServiceID = MemberService.ServiceID
AND GymService.ServiceName LIKE 'Aerobics'

What was the problem we had? The table that we needed information from contained only IDs not names. The input given was a service name (Aerobics) and the output sought was also names and addresses. The member ID to name mapping is in the GymMembers table while the serviceID to name mapping is in the GymService table. A good understanding of the concept of foreign keys is essential in writing Join queries in SQL. When a primary key of a table appears as a column in any other table, it is called a foreign key. In the Memberservice table, we have two foreign keys: ServiceID and MemberID. To hook up tables, all we have to do is hookup the foreign key in one table with the corresponding primary key in the other table after the WHERE clause. This is exactly what the following parts of the command did

WHERE MemberService.MemberID = GymMembers.MemberID
AND GymService.ServiceID = MemberService.ServiceID

In each case, we used the syntax Table Name. Column Name in order not to confuse the database engine - the names of the columns are the same, so this is the only way to distinguish each of them.

Also, we listed all concerned tables (gymservice,gymmembers, and memberservice) after the FROM clause with a comma between each table's name. Hooking up of the foreign keys with primary keys and the listing of all the tables’ names constitute the bulk of the JOIN clause. What is left is simply setting the input and getting the output.

SELECT GymMembers.MemberName, GymMembers.MemberAddress

The statement above gets us the output we want. Interestingly, specifying the table name before the column name is not necessary here because there is only one column called 'MemberName' and another called 'MembrAddress' in all three tables. We just put it in to make things clearer. The following line indicates our input condition:

AND GymService.ServiceName LIKE 'Aerobics'

Screen Shot 7a: Result of the JOIN query