In this post we illustrate SQL joins using a common example of geographic data in a Microsoft Access database. Here we have a fictional list of coffee shop locations in Canada. We have some made-up sample data that includes store number and addresses, among others. Our goal here is to be sure our data is designed correctly. Perhaps we are planing to develop the ultimate coffee shop website that will eventually include pictures and reviews of each coffee shop in Canada and the US.

Here is some sample data. Eventually we could have tens of thousands of entries. Users of the application will narrow down their selections by first entering a country, then state/province then city and coffee company. In this way we don’t need to transfer the entire list to the browser which would be too slow. Because of this we need to create more tables. We need to have a Country table, a State table, a City table and a CoffeeCompanies table.

ShopLocations Table

Here is our sample data of six records. Notice that there is a Richmond British Columbia and a Richmond Ontario.

Joins – Incorrect

Below is a join that exposes two problems with our database. First, we are missing Kamploops in the Cities table. That is an easy fix. We just need to add it to the Cities table. The more challenging one is that we have duplicates of Richmond that cause us to show more than six records in this query. We need a different query.

In the above query result we have 8 rows instead of 6. A problem. Also, Kamloops is in the Shop Locations table, our main data table, but it is not in the Cities table.

City and State

Have a look at the join between the ShopLocations and Cities tables. There is one join. The problem is that knowing the city is not enough information. You need to know the city and the state to know the location of the city. There are more than one Richmonds in Canada. There are more than one Torontos in North America. If we knew the city and state/province we could identify the country. Let’s look at our other tables.

Countries Table

States Table

Cities Table

In the mean time we added Kamloops to the Cities table because we had forgotten about it.

Make a new Cities Table

We can make a new Cities table called CitiesWithStateNames. We can then use this table in our query to eliminate duplicates. Here below is what Access’ make table query looks like and below that is what the table’s data looks like. It is not enough to just link from the ShopsLocation to the Cities table with only the CityName as the criteria. We also need to know what State that city is in because we have two Richmond’s.

Joins – Corrected

The difference here is that we have added a StateName to a new table CitiesWithStateNames. We have also added a join as well.