< Blog

Subselect Query for Reports (SQL)

By Garth Jones

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.

Subselect Query for Reports (SQL)

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.

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

               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

               (

           )

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