INNER JOIN
|
When we use databases in reality, more often than not we want to get related data from multiple tables at the same time. In order to do this we need to connect or "join" tables together in our queries. There are a few types of joins, we are going to focus on using an INNER JOIN.
The need to join tables is why we have primary and foreign keys. When a person with CustomerID 7 makes an order, we add a row of data to the Orders table that includes their CustomerID. By doing this we can easily link the orders that they have made with them. Something to remember: a foreign key on one table is simply the primary key from another table. It's the main identifier for a piece of data on that table, like CustomerID 7 is for Tuncan Dyler in our database. |
|
|
Making INNER JOINs
To obtain data using an INNER JOIN, we first make a normal SELECT and just choose the data that we want from both tables. We then choose the main table and put that in the FROM section, then we INNER JOIN to the other table using the IDs. An example of the structure is below: SELECT tableOne.valueOne, tableTwo.valueTwo FROM tableOne INNER JOIN tableTwo ON tableOne.primaryKey = tableTwo.foreignKey; On the right is an example of a simple join that shows the name and date of different orders that people have made. There are multiple rows with the same name because they have made multiple orders. |
|
If we wanted to find only orders made by Tuncan then we would just add a WHERE on the end like below:
Task: Make a query like the one above searching for all orders by "Sark."
JOIN Challenges
1) Retrieve the OrderID, OrderDate, and ProductName for all orders.
2) Retrieve the OrderDate, Sent status, and ProductName for all orders.
3) Show the OrderDate and the PreferredName of the customer for each order.
4) List the OrderID, ProductName and Price for all orders.
2) Retrieve the OrderDate, Sent status, and ProductName for all orders.
3) Show the OrderDate and the PreferredName of the customer for each order.
4) List the OrderID, ProductName and Price for all orders.
Joining Across Multiple Tables
|
Very often when doing queries we need data from multiple tables at once. Think about our current situation, it would be really helpful to have the name of a customer, the product that they have ordered, and whether it has been sent or not. This situation will require us to do 2 joins in one query. Looking at the picture on the right, to get this information we need to join Customers with Orders, then also join Orders with Products - there is no way to join Customers directly with Products.
|
|
Important note: Microsoft Access SQL differs slightly from most versions of SQL in how it does this, this is demonstrated below.
|
|
Essentially (to perhaps oversimplify) with Access SQL we need to put in the brackets like the example on the left. We start the brackets after the FROM and before the main table, then we finish them at the end of the first INNER JOIN. Then we can do our next INNER JOIN.
Task: Copy the example on the left (above), run it and see what happens.
Task: Copy the example on the left (above), run it and see what happens.
"Multiple JOIN" Challenges
1) Retrieve the OrderID, OrderDate, ProductName, FirstName, and LastName of the customer for all orders.
2) Retrieve the FirstName and LastName of customers, the OrderDate, and the Price of the products they ordered.
3) Display all orders placed by customers born after January 1, 2000, include their name, ProductName and the Price.
4) Retrieve the OrderDate, the Price of the product, and the customer’s PreferredName for all orders where the product price is greater than $50.
5) Display the OrderID, ProductName, Sent status, and the full name of the customer for all delivered orders.
6) List the OrderDate, ProductName, Price, and the PreferredName of customers for all orders that have not been sent.
7) List the full name, and the name of all the products ordered by the Customer with the PreferredName "BoogieBoard".
2) Retrieve the FirstName and LastName of customers, the OrderDate, and the Price of the products they ordered.
3) Display all orders placed by customers born after January 1, 2000, include their name, ProductName and the Price.
4) Retrieve the OrderDate, the Price of the product, and the customer’s PreferredName for all orders where the product price is greater than $50.
5) Display the OrderID, ProductName, Sent status, and the full name of the customer for all delivered orders.
6) List the OrderDate, ProductName, Price, and the PreferredName of customers for all orders that have not been sent.
7) List the full name, and the name of all the products ordered by the Customer with the PreferredName "BoogieBoard".
