Using subqueries
Follow along:
There are fake data tables that accompany this article available to download off the NICAR Web site. Go to www.nicar.org and click on "Uplink Tech Tip Downloads." Inside there you'll find a self-extracting file called "mjtricks" that can be downloaded. Inside are a Microsoft Access database (1997 version) and Dbase IV files of the same data in case you are using FoxPro.
When working in a database manager, subqueries can prove immensely useful once you master the slightly complex syntax. Essentially subqueries allow you to come up with an answer based on another query that is imbedded within your main query.
That probably sounds confusing, but keep reading. It makes more sense when you see them in action. So I've got a couple examples of how you could use a subquery for you to follow along.
The first is fairly straightforward: I've got a list of inspections, where there could be multiple records for the same facility. But I want to find the last (or most recent) inspection for each facility. So my result would have one record for each facility with the date of the most recent inspection.
The second example has a couple levels to get to the final answer: I've got a list of discipline actions (in this case against police officers) and I also have a list of promotion dates that includes the person's rank. I want to find out what rank each officer was at the time they were disciplined. My result would have one record for each discipline action and include the date of the action and their rank at the time.
I've created some fake data tables for this demonstration that can be downloaded off the NICAR Web site. Go to www.nicar.org and click on "Uplink Tech Tip Downloads." Inside there you'll find a self-extracting file called "mjtricks" that can be downloaded. Inside are a Microsoft Access database (1997 version) and Dbase IV files of the same data in case you are using FoxPro.
As you will quickly see, these are very basic tables with bare-bones information just to show how this works. If you were working with real data, it would likely have many more fields and other details you might want to include.
Inspections
Let's start with the inspections data. The table is called "inspect." There you will find the site number (unique ID for each facility), the date of inspection and type of inspection. Notice there are multiple records for some facilities, but not all.
In this case we want to find the most recent inspection date for each facility. To do this, we'll use the MAX() function imbedded within a subquery in our select line. Here's what the SQL looks like, then I'll explain:
SELECT sitenum, (select max(b.inspdate) from inspect b where a.sitenum=b.sitenum) as lastinspect FROM inspect a GROUP BY sitenum
The select line within the parentheses will look for the largest (or in this case most recent) inspection date and the "where" clause specifies that it will compare only those records that have the same site number. If you were to leave that off, the query will run but it will give you the exact same date for all records (the most recent date that exists within the whole table). You need to GROUP BY in order for your results to yield just one record for each facility.
In this case I've named the field where my subquery results will show up as "lastinspect," and I used aliases for each of the table names simply to have less typing. ("a" and "b" are the aliases). I'll use this type of naming in the next example as well.
Discipline
This one involves a couple steps. There are two tables, "discipline" and "promotions." The promotions table lists the badge number, the rank and date of promotion; there might be multiple records for the same person if they were promoted more than once. The discipline table, which also might have multiple records, lists the badge number, the discipline action and date it occurred.
The first thing we need to do is to create a new table that lists start and end dates for each rank the person held. So for each badge number that shows up in the database, this query will look at the rank and list the promotion date as the "start date" and then look for the next rank (chronologically by date) and subtract one day to come up with the "end date." Then it goes on to that second rank and does the same. In cases where there isn't another rank, it will leave the "end date" blank.
Here's the SQL:
SELECT a.badge, rank, a.promodate as start, (select min(b.promodate)-1 FROM promotions b where b.badge=a.badge and b.promodate>a.promodate) as end FROM promotions a
Create this into a new table. Here I'm going to call it "dates." The next step then is to match this new "dates" table with the discipline table to put the correct rank with the date the officer was disciplined. This uses an inner join, which is easily accomplished in Access by drawing the line between the two tables in the grid view. Join it between the badge field on each table. Then when you open the SQL view, you can add in a few pieces of information to come up with the following:
SELECT discipline.badge, start, end, rank, actdate, action FROM DISCIPLINE INNER JOIN dates ON DISCIPLINE.BADGE = dates.badge WHERE (actdate>start) and (actdate<end or end is null)
In addition to joining on the badge number, the WHERE criteria also specifies that we want to match up the discipline action date where it falls after the "start" date and before the "end" date in the dates table.
The resulting query yields the answer to our original question.
Other uses
Another use for subqueries is to find any records from one table that do not show up in another table. Conversely you could also use it to find those records that match.
For example, if you slice records out of one table and create a new table. Then you want to go back and find those records from the original that did not end up in the new table. It works best if you have a unique ID that shows up in both tables. Here's the syntax:
SELECT * FROM table1 WHERE uniqueid not in (SELECT uniqueid from newtable)
To find the records that match you would simply remove the "not" from the where clause.
There is more information about subqueries in the Microsoft Access help file and in the Microsoft Knowledge Base at http://support.microsoft.com/directory/. There you can also find details on how to run these queries in the grid view.
MaryJo Sylwester can be reached by e-mail at maryjo@nicar.org