Gear

MySQL Table Subqueries

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).

EquipTitle

id name
1 Fancy Camera
2 Cool Microphone


EquipUnique

id EquipTitle_id identifier
1 1 A
2 2 A
3 2 B


ServiceIssue

id EquipUnique_id status desc
1 1 3 Flip out screen is loose
2 3 3 Handle scratched
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:

1
2
3
4
id |  name             | available
----------------------------------
 1 | "Fancy Camera"    | 1   
 2 | "Cool Microphone" | 1

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT ETitle.id,
       ETitle.name,
       IFNULL(EUnique.available, 0)
FROM EquipTitle AS ETitle
LEFT JOIN (
  SELECT EquipTitle_id,
         COUNT(id) AS available
  FROM EquipUnique
  LEFT JOIN(
    SELECT EquipUniqueItem_id,
           MAX(status) AS maxStatus
    FROM ServiceIssue
    GROUP by EquipUnique_id
  ) AS MaxIssue
  ON EquipUnique.id = MaxIssue.EquipUnique_id
  WHERE maxStatus < 5 OR maxStatus IS NULL
  GROUP BY EquipTitle_id
) AS EUnique
ON ETitle.id = EUnique.EquipTitle_id

To work through this, I constructed it in steps. Let’s start with a list of all EquipTitles (returning columns name and id):

1
2
SELECT ETitle.id, ETitle.name
FROM EquipTitle AS ETitle

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.

1
2
3
4
SELECT ETitle.id, ETitle.name, EUnique.key_id
FROM EquipTitle AS ETitle
JOIN EquipUnique AS EUnique
ON ETitle.id = EUnique.EquipTitle.id

This is not what I want.

However a subquery that generates a virtual table will do the trick

1
2
3
4
5
6
7
8
9
SELECT ETitle.id, ETitle.name, EUnique.count
FROM EquipTitle AS ETitle
JOIN (
  SELECT EquipTitle_id,
         COUNT(id) AS count
  FROM EquipUnique
  GROUP BY EquipTitle_id
) AS EUnique
ON ETitle.id = EUnique.EquipTitle_id

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.

1
2
3
4
SELECT EquipUnique_id,
       MAX(status) AS maxStatus
FROM ServiceIssue
GROUP by EquipUnique_id

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- SELECT ETitle.id, ETitle.name, EUnique.count
-- FROM EquipTitle AS ETitle
-- JOIN (
--   SELECT EquipTitle_id
--          COUNT(id) AS count
--   FROM EquipUnique
  LEFT JOIN(
    SELECT EquipUniqueItem_id,
           MAX(status) AS maxStatus
    FROM ServiceIssue
    GROUP by EquipUnique_id
  ) AS MaxIssue
  ON EquipUnique.id = MaxIssue.EquipUnique_id
  WHERE maxStatus < 5 OR maxStatus IS NULL
--   GROUP BY EquipTitle_id
-- ) AS EUnique
-- ON ETitle.id = EUnique.EquipTitle_id

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.

1
2
3
SELECT ETitle.id,
       ETitle.name,
       IFNULL(EUnique.available, 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.