The Subselect Query

Last updated on August 2nd, 2022 at 10:52 am

Earlier, in my blog posts from last week I talked about how queries are processed and how a Not Equal To operator is processed. At any rate, in this blog post I will help you to write a subselect query in order to determine what PCs are missing a specific type of software.

Altogether, a subselect query is made up of two parts: the positive query and the negative query. This might seem backwards, but in order to ensure that you get the results that you are looking for you need to exclude all PCs with a positive result.

Subselect Query Table Example

As an illustration, let’s use the same table from the previous blog posts for all of our example queries. Basically, this table is comprised of 4 PCs and their add/remove program (ARP) details.

Additionally, using pseudocode I will write the negative query this way:
Above all, find all PCs where the ARP display name is Not Equal To Microsoft Project 2010

A lot of people would expect to get PC1 and PC3 returned in the result set, but that is not the case as I demonstrated in my previous blog post.

Process

However, in order to achieve the desired results we need to break the query into two parts as I explained earlier; A positive query and a negative query.

Positive query

As a result, here’s the pseudocode for the positive query:

e.g. Find all PCs where the ARP display name is Equal To Microsoft Project 2010

After all, you think that PC2 and PC4 should be returned to the result set then you would be correct, but we only want to know what PCs do not have Microsoft Project 2010 installed.

Accordingly, this brings us to the subselect query. After all using the positive query, we know which PCs have Microsoft Project 2010 installed. As a result, combining it with the negative query we will find out which ones do not have Microsoft Project 2010 installed.

I re-write the pseudocode combining both the positive and negative queries:
e.g. Find all PCs where the PC is not in (Find all PCs where the ARP display name is Equal To Microsoft Project 2010)

First this query will find all of the PCs with Microsoft Project 2010 installed and then it will exclude those PCs from the list of all PCs. Therefore the end result will only display those PCs which are not in the bracketed part of the query. Finally, PC1 and PC3 show up in the result set!

Up until this point, we have talked about how to write queries in a generic sense, but these same principles can be applied to either SQL or WQL queries. Now that you know more about how queries are processed, the next step is to create queries for customized reports (SQL) or collections (WQL).

Followups

As a result, in my next two blog posts, I will show you how to create queries for both of these items.

Finally, don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. Additionally, If you have any questions, please feel free to touch base @Garthmj.