|
by Molly Pell, Quality Assurance Specialist
When querying data from multiple tables in Microsoft Access or SQL Server, we usually use Inner Joins to link records with values that exist in both tables. But what if we need to find records that exist in one table but not the other?
Data Access Layer using a Microsoft Access 2003 Database Trying to pass a parameter to a query on a datset over a access database. Using the following SQL statement SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, DiscontinuedFROM ProductsWHERE (CategoryID = [catid]) The where clause in the statement keeps putting it. How To Query Multiple Databases and Generate Reports Dr. Ramon Lawrence. SELECT O.custId, C.custName, SUM(totalAmount). Several database products, such Microsoft Access, Microsoft SQL Server, Sybase, IBM, and Oracle, have the ability to 'link' a table from another source into the current server instance. How to instantly convert Sybase SQL Anywhere database to Access using Full Convert Enterprise database converter. Microsoft Access, MS Access (related files: mdb, accdb). If you do know your database, do select it from the list on the left. Connection to target Access database List on the left shows all the databases which can be filled. Microsoft Access usually requires a unique index on the server. Create an index on the primary key. Then, select the primary key as a unique record identifier when linking the table in Microsoft Access.
It turns out we can do this without any programming since it's part of the query SQL syntax. Simply use an Outer Join to generate 'Not In' results. Use the LEFT JOIN or the RIGHT JOIN syntax depending on which table is referenced first in the query:
- LEFT JOIN returns all records from the first table, even if there are no matching records in the second table.
- RIGHT JOIN returns all records from the second table, even if there are no matching records in the first table.
These Joins can also be used to only retrieve the record values that exist in one table and not the other.
There are several scenarios where Outer Joins should be used:
The following examples use the Customers and Orders tables from the Northwind 2007.accdb sample database to demonstrate these scenarios.
Assume that we want a list of all customers, and if they have any Orders, we want to show the order dates. In this scenario, we want to see customers with orders AND customers without orders. We can use a Left Outer Join to accomplish this.
Designing the Query
Follow these steps to design the query interactively (or paste the Query SQL instead):
- In the Designer Window, add the Customers and the Orders tables. The Join line is automatically added to join [Customers].[ID] to [Orders].[Customer ID].
- Double click on the line to open the Join properties
Notice that this shows the name of the Left table and the Right table, and lets you choose your Join option. In this case, we want option #2: “Include ALL records from ‘Customers’ and only those records from ‘Orders’ where the joined fields are equal.” - Click OK to return to the Designer Window, and add the fields [Customers].[Company] and [Orders].[OrderDate]
The designer window should look like this:
Query SQL
The SQL statement for the query is as follows: Panasonic rx ct 900 manual lymphatic drainage.
Query Results
Run the query, and you see all companies and their orders, if any. If the company has no associated order, it still shows in the list, only with a Null Order Date.
If we used an Inner Join, then Company B would be excluded from the results, since there is no matching record in the Orders table.
Now assume that we ONLY want to find the customers who have not placed any orders. Just like in the first scenario, we will use a Left Outer Join to accomplish this. This time, however, we’ll add criteria to exclude the rows that have orders.
Designing the Query
In the Designer Window, add the Customers and the Orders table, and set the Join type to be a Left Outer Join (as explained in Steps 1 and 2 above).
In the Designer Window, and add the following fields:
Field | Show | Criteria |
---|---|---|
[Customers].[Company] | True | Is Null |
[Orders].[Order ID] | False |
The designer window should look like this:
Query SQL
The SQL statement for the query is as follows:
Query Results
Run the query, and you see only the companies who have no associated orders:
Now assume now that we want to find customers who have not placed orders in the last year. The steps are similar to finding records that exist in one table, but not another, except that rather than joining the Customers table to the Orders table, we join it to a saved query that returns Orders within the last year.
First save the following query as 'qryOrdersLastYear':
And follow the steps above, only using qryOrdersLastYear instead of Orders. The SQL statement for the query is as follows:
Outer joins also come in handy when we want to troubleshoot missing data. For instance, if we find ourselves with orphaned Order rows, we can find which orders don’t have an associated customer.
Designing the Query
Follow these steps to design the query interactively (or alternatively, you can paste the Query SQL below)
- In the Designer Window, add the Customers and the Orders table. The Join line is automatically added to join [Customers].[ID] to [Orders].[Customer ID].
- Double click on this line to open the Join properties:
Notice that this shows you the name of the Left table and the Right table, and lets you choose your Join option. In this case, we want option #3: 'Include ALL records from ‘Orders’ and only those records from 'Customers' where the joined fields are equal.' This is a Right Outer Join, since the Orders table is our Right table.
Click OK to return to the Designer Window, and add the following fields:
Field | Show | Criteria |
---|---|---|
[Orders].[Order ID] | True | |
[Orders].[Customer ID] | True | |
[Customers].[ID] | False | IsNull |
The designer window should look like this:
Query SQL for Missing or Orphaned Records
The SQL statement for the query is as follows:
Query Results
Run the query, and you see any orphaned Orders records (orders that have a non-existent customer). For instance, if Customer 1 is missing from the Customers table, the results are:
(Note that Referential Integrity was removed from the relationship for the purpose of this example. Enforce Referential Integrity to prevent orphaned records. See http://support.microsoft.com/kb/304466 for details.)
We can also use Outer Joins to find rows that are missing between tables. Assume that we have two copies of the Customers table, but records were added and deleted from both tables. We need to see see the records from both tables that aren't in the other.
To see rows in Customers that are not in Customers1, the Query Designer looks like:
and the SQL is:
Sybase Select Statement
To see the opposite (rows in Customer1 that are not in Customers), the designer looks like:
and the SQL is
Microsoft Access Select Query
Additional Resources
Sybase Select Case
- Microsoft KnowledgeBase Article: Defining relationships between tables in a Microsoft Access database