sub-select query

As promised, here is the query within a query solution that simplifies the last article’s idea.

We saw in my last post how a question like “Who was the first person to borrow a book?” could be solved by having a query use the results from another query. That required 2 actual queries saved separately. Now let’s see that same concept done in a single saved query.

Let’s break the problem down into steps.

Step 1 – determine the earliest case of a book being borrowed

SELECT Min(tblBorrowing.dtmDateDue) AS EarliestDate
FROM tblBorrowing
WHERE (((tblBorrowing.lngBookID)=1));

I have shown the raw SQL above (good idea to learn that stuff!), but you could have just used the query builder to create a query like this following.

Note my use of a custom field name in “EarliestDate”.

Step 2 – Utilize the first query in a where clause

Note how I went back and created the original query style, utilizing all the tables and desired fields, and then added the first query as a criteria for the date field.

This creates a single saved query, rather than 2 separate ones. Here is the resulting (and somewhat hard to read!) SQL code.

SELECT tblCustomers.strCustomerName, tblBooks.strBookName,
tblBorrowing.dtmDateDue
FROM (tblBooks INNER JOIN tblBorrowing ON tblBooks.lngBookID =
tblBorrowing.lngBookID) INNER JOIN tblCustomers ON
tblBorrowing.lngCustomerID = tblCustomers.lngCustomerID
WHERE (((tblBooks.lngBookID)=1) AND tblBorrowing.dtmDateDue =
(SELECT Min(tblBorrowing.dtmDateDue) AS EarliestDate
FROM tblBorrowing
WHERE (((tblBorrowing.lngBookID)=1));
));

I will admit, however, that I often clutter my databases with the 2 (or more!!!) query solutions as discussed last week. I find them easier to comprehend and fine tune.

If you want a cleaner looking Queries tab, use this sub-select version!