Subqueries Primer: Find Employees With Salary Higher Than Their Department Average

This is a classic SQL problem- probably more a textbook exercise than a real-life business problem, but a classic nonetheless. "Find all employees whose salary is higher than the average salary for their department."

In Access, the most straightforward approach is a multi-query solution. However it can be also be accomplished in a single query using subqueries. I'll discuss both approahces here

Read more »

Subquery: Add Missing Master Records

Let's say you have some data to add to your detail table, but some of the master records are missing. Here's a example query that uses a subquery to add "missing records" based on matching an ID field between two tables.

INSERT INTO tblMasterRecords ( MyID, MyText )
  SELECT MyID , "New Master record"
  FROM tblNewData
  WHERE NOT EXISTS
      (SELECT * FROM tblMasterRecords WHERE MyID=tblNewData.MyID);

Some things to note here:

• The query in the brackets is called a subquery.
• The EXISTS condition resolves to true when the subquery returns any records. By using this in the WHERE NOT EXISTS syntax, the main query returns records from tblNewData only when the subquery doesn't return any records.
• Inside the subquery, the ID of current record in the main query is referenced as tblNewdata.MyID.

If My Filter Doesn't Find Any Records, Show Me All the Records Instead!

Here's a neat little SQL subquery. The request was: If no records match the specified criteria show me all records instead. I think it was to be used in a form where the user would be able to browse the data. They'd use keyword to subset the data, but the design point was to never not show any records.

This can be done using the Exists keyword. Here's an example:

SELECT Clients.*
FROM Clients
WHERE
  (Clients.ClientCode Between "BC" And "BZ")   
  OR   
  (NOT EXISTS
    (SELECT * FROM Clients WHERE Clients.ClientCode Between "BC" And "BZ")
   ) ;

The EXISTS condition resolves to true when the subquery (inside the brackets starting with SELECT) returns any records. In this case I use NOT to reverse that. So if the condition WHERE Clients.ClientCode Between "BC" And "BZ" doesn't find any records, the criteria staring with NOT EXISTS is true, and so all records match the complete criteria (because of the OR) and the query returns all the rows in the table.

Count of unique items and count of all items in the same query

If you've scanned this blog you'll know of my fascination for subqueries. This thread at UtterAccess.com caught my eye today. The poster needs to count disticnt entries in one column, and all entries in another column, all grouped by the value in a third column.
Sounded to me like three queries. One for the each count and one to produce the final result. The poster, though, challenged the UAers to do it in one.
Van T. Dinh's post is a nice clean all in one query SQL solution. It's a good example of taking the two or three queries you would have done separately and building them up into one query, essentially by surrounding each with brackets and then putting it in the FROM clause like a table.
There are a couple of other posts here on my blog that put the whole subquery in the FROM clause.

• Writing a Count(Distinct) Query in Access
• More subqueries

** Post links cleaned up 2011-09-05 **

Returning Records around a specified ranked Record

Here's a really neat post that uses subqueries to return records "near" a particular record when a bunch of data is ranked.

Quote:
Sometimes, for example when you are dealing with a large table of data that you wish to have ranked, such as a sports league table, you may wish to return the ranking of a particular record, together with some records ranked just before this record, and some just after.

Subqueries: include a running total in a query

Here's another great subquery example from UtterAccess. This one uses a simple subquery to add a running total to a query.

Subqueries: find top 25 by group

Here's an interesting post on UtterAccess where one of the VIPs uses a subquery in a pretty clean and simple way to find top 25 by group.

Poker Series Ranks Using a Simple Subquery

Here's a post at UtterAccess that demonstrates using a simple subquery to calculate ranks.

Ranks are a common challenge for folks using SQL queries to get at their data. This basic example demonstrates a simple technique and includes an example database.   #

More subqueries

Here's another cool subquery. The use of a subquery here allows a left join on a calculated field!

SELECT BatchID, NewSKUString, CleanedSKUNumber, SKUID, PROD_NBR, OFFRNG_ENG_DESC, CORP_STAT_CD
FROM
  [SELECT BatchID, NewSKUString, CleanSKUNumber(nz(NewSKUString),True) AS CleanedSKUNumber FROM tblNewSKULists]. AS NewSKUs
  LEFT JOIN tblExistingSKUData
  ON NewSKUs.CleanedSKUNumber = tblExistingSKUData.PROD_NBR;

(CleanSKUNumber is a function)

I read something on UtterAccess that said that when you use square brackets as above Access creates a separate query "behind the scenes". I don't know if that's true or what difference it makes. I can tell you that the above works just the same with round brackets:

SELECT BatchID, NewSKUString, CleanedSKUNumber, SKUID, PROD_NBR, OFFRNG_ENG_DESC, CORP_STAT_CD
FROM
  (SELECT BatchID, NewSKUString, CleanSKUNumber(nz(NewSKUString),True) AS CleanedSKUNumber 
   FROM tblNewSKULists) AS NewSKUs
  LEFT JOIN tblExistingSKUData 
  ON NewSKUs.CleanedSKUNumber = tblExistingSKUData.PROD_NBR;

I was also fascinated to see that if you key the above into the SQL view and then switch back to the QBE grid it shows the subquery as it were a sepeartely saved query.

Subqueries

I am fascinated by subqueries and keep finding things on-line that I didn't know would work. Some of the really elegant subqueries I've created have turned out to be reall performance problems. Others work fine and result in an all-in-one solutions that seems "smarter" and mean less components to manage.

I found one today in a post on the Access team blog. Here's one of the queries from the article:

SELECT C.Color, Sum(C.Value) AS Total, T2.N
  FROM
    (SELECT T.Color, Count(T.Color) AS N
     FROM
      (SELECT DISTINCT Color, Count(*) AS N
       FROM tblColors GROUP BY Color) AS T
       GROUP BY T.Color) AS T2
  INNER JOIN tblColors AS C
  ON T2.Color = C.Color 
  GROUP BY C.Color, T2.N;

This is for count of distinct items. See the blog post for more.