Subselect Query for Reports (SQL)

Last updated on July 24th, 2022 at 07:33 pm

This post is a continuation of my series about how queries are processed. Please start with the first blog post in this series for more details. As a Microsoft Endpoint Configuration Manager (MEMCM / MECM / SCCM / ConfigMgr ) reports, which is really SQL Server Reporting Services (SSRS), Can be seen a hard. Once you get the hang of it, it really isn’t that bad!

The problem

Today’s blog post will show you how to create a subselect query in order to discover which PCs do not have Microsoft Project 2010 installed using T-SQL (generally called SQL) for use in SQL Server Reporting Services (SSRS) reports.

Here are some subselect query writing tips to always keep in mind:
· For the positive query don’t return the PC name because of the possibility of duplicate PC names; instead return the ResourceID information.
· Remove any unnecessary SQL views.

Positive SQL Query

Below is the positive SQL query to find all PCs that have Microsoft Project 2010 installed.

Select

                ARP.ResourceID

From

                dbo.v_ADD_REMOVE_PROGRAMS ARP

Where

Negative SQL Query

                ARP.DisplayName0= ‘Microsoft Project 2010’

Below is the negative SQL query to find all PCs that have Microsoft Project 2010 installed.

Select   

                R.Name0

From

                dbo.v_R_System R

Where

                R.ResourceID not in

                (

            ) 

Subselect Query for Reports

Now, here is the subselect query where I combine the above positive query with the negative query to find all PCs that do not have Microsoft Project 2010 installed.

Select   

                R.Name0

From

                dbo.v_R_System R

Where

                R.ResourceID not in

                (

                                Select

                                                ARP.ResourceID

                                From

                                                dbo.v_ADD_REMOVE_PROGRAMS ARP

                                Where

                                                ARP.DisplayName0= ‘Microsoft Project 2010’

            ) 

Hopefully this gives you more insight into creating and using subselect queries for SQL reports.
Next week, I will show you how to create a subselect query for collections (WQL).

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.