In this post we illustrate SQL joins using a common example of geographic data in a Microsoft Access database. Here we have a list of coffee shop locations in Canada. We have some sample data that includes store number and addresses, among others. Our goal here is to be sure our data is designed correctly. We plan 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.
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. Let’s look at our other tables.
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.