Learning to use table subqueries in MySQL has really helped to advance my understanding of SQL and to demonstrate its versatility. I recently refactored a project to make use of a SQL query to derive the number of available pieces of gear from an inventory database. At the time when someone is making a reservation for gear, they need to know how much of each item is available. The quantity changes as equipment accumulates damage from wear and tear.
Three tables describe the equipment inventory. The EquipTitle table has an entry of each type of gear. The related EquipUnique table has a row representing each piece of gear. For example, we have 20 identical Canon XA10 video cameras. When people make a request for an XA10 camera, they don’t care which of the 20 they receive, the choice is simply “XA10 Camera” (the record in the EquipTitle) and they can tap on it to increase the quantity (up to the maximum number of records in the EquipUnique table). But a complication to the available quantities are service issues that force the staff to pull an item out of circulation. Issues with pieces of gear are tracked in the ServiceIssue table, which maintains a relationship to the EquipUnqiue table. In this simple example, the inventory includes one camera (camera A) and two microphones (mics A and B).
|1||1||3||Flip out screen is loose|
|3||3||5||Power Capsule Broken|
|4||3||6||Audio has static|
Status indicates the severity of an issue. The Fancy Camera has a minor issue that needs to be documented, but doesn’t affect its availability. One of the Cool Microphones (Mic B) is a hot mess with one minor issue and two serious issues (status 5 or higher) that prevent it from being in circulation — a bad power capsule and a loose internal connection that’s causing static.
The result should be the following table rows:
The query needs to recognize that the issue attached to the camera should be ignored, there are no issues on Mic A which indicate that it’s fully available and Mic B needs to be evaluated based on the maximum status of its many issues.
Here is the MySQL query I used to derive that result:
To work through this, I constructed it in steps. Let’s start with a list of all EquipTitles (returning columns name and id):
Now let’s add to that a count of EquipUnique items for each EquipTitle. A simple join to related EquipUniques is going to expand the number of rows to include all records in EquipUniques.
This is not what I want.
However a subquery that generates a virtual table will do the trick
EUnique is now a derived table and the fields SELECTED are available in the outer part of the query. Aliasing the derived table is required.
The final step is limiting the count of EquipUniques to just the ones that are below the “serious” status threshold in service issues. One of the nice things about working with subqueries is that they nest easily. Instead of adding more complexity to the query I’ve go so far, I’ll demonstrate the service issue subquery as a stand-alone query.
This generates rows of EquipUnique items with the maximum status value from any related ServiceIssue records. The final step is inserting this into our previous query as another JOIN and filtering by the maxStatus value with a WHERE clause. I want to be sure to include gear that has no issues so the WHERE clause allows maxStatus to be NULL as well.
As the final touch, we’ll change “count” to “available” to better represent the meaning of the column and add in a default value of 0.
This is only a demonstration of subqueries used in a JOIN clause. They can be used in other parts of a SQL query. This approach happens to solve the problem I had on my plate and I like how easy it is to reason about when the subquery is so neatly self-contained as a SQL query unto itself.