AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com 


  

Section Menu 

Are you a University or College bookstore and want to order multiple books?  Email us at alphapress@hotmail.com or call us at 518-250-3890.  We will be happy to talk to you.  

Are you a professor and you need a sample copy?  We will be happy to send you a pdf or printed copy!  Email us at alphapress@hotmail.com.

Are you outside the United States?  We can ship multiple books worldwide in most countries.

  

 

 

Understanding Inner Joins

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


Inner Joins

Find customers who actually have some orders

Discussion:

Let us assume that our supervisor has a very simple request:  She wants a report of customers who actually ordered something from us.  By the way, the customers table might include people who asked for quotations or leads, or it might include customers who have not ordered anything for some time.  How can we answer this request?  Of course, we can go back to the chapter about duplicate, orphaned, and related records and use a subquery such as: 

Code:

SELECT *

FROM Customers

WHERE CustomerID

IN (SELECT CustomerID from tbls_Orders)

 

Result:

Notice that the number of customers returned is only 190.  However, in the customers table, there are 201 customers.  So, 11 customers have not had any orders at all for the historical data we have.

Why, however, should be use a subquery since we can do the same thing in no time using a join?  Let’s create a new query in design view and add the customers and orders tables.  There is a one-to-many relationship between customers and orders.  For one customer, there might be multiple orders, but each order definitely belongs to one customer. 

Double-click on the relationship line between customers and orders.  The following dialog box comes up.  By default, Access 2007 will join two tables through an inner join or, in other words, include records from each table where there is a common CustomerID field.  The CustomerID field is the primary key in the customers table and the foreign key in the orders table.

This is the case of an inner join.  Notice that an inner join will give us the customers who have orders.  If there are any customers without orders, they will not appear in the result set.  In addition, if there are any orders without associated customers, they will not appear either.  Only where the two tables match on the CustomerID will records be returned.  The SQL code for the inner join appears below.  Notice that the database returned 1000 records.  This is because there are 1000 cases in which the CustomerID in the customers table has an associated CustomerID in the orders table.  The eleven customers without a CustomerID in the orders table will not appear in the result set of this inner join.  

Code:

SELECT FirstName, LastName, Address, OrderDate, ShippingCost

FROM Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

 

Result:

 

Discussion:

The initial request, however, was to present a list of unique customers who have orders.  We do not need any repeated customer names in the result set.  To achieve this, we need an inner join and a GROUP BY clause by last name, first name, and address fields.  Notice how the number of customers returned is the same as that returned by the subquery we used in the beginning of this example. 

Code:

SELECT FirstName, LastName, Address

FROM Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

GROUP BY FirstName, LastName, Address

 

Result: