How to Combine Two Views into one Query

Last updated on December 29th, 2023 at 12:47 pm

In this blog post, I will show you how to How to Combine Two Views into one Query.

The other day, I was reading one of the various groups that I answer questions in. There was an interesting post that asked. (Edited) “I have been asked to see if there is a way to see who is using new teams. I found that new teams listed in v_GS_WINDOWS8_APPLICATION (W8A). While the version of teams (machine wide) is showing in v_Add_Remove_Programs (ARP). I can do simple reports. I was able to do two separate queries but I wanted to see if it’s possible to combine them so that the results would be computer x this version in ARP and version in W8A or if one of them is null. Is that just done by the table somehow or do I have to combine the queries somehow?”

I have to admit that when I first saw this, I thought they wanted to see a union count of Teams version. But when I reread the request, to answer this person. I realized that this is just a left outer join request.

Let’s answer the left outer join question.

Combine Two Views into one Query

To complete this take, we will need three queries in total. The first query is all versions of Team within Add/Remove Programs (also called Program and Features). The second query is All devices with Team installed using the Store apps (called Windows 8 Application (W8A) in ConfigMgr). The final query we will want is to leverage Common Table Expressions (CTE) with Left outer joins to create one report that will show if a device has either version of Teams Installed.

Query 1

This first query will return all devices with the Team store app installed. It also uses a trick to use a star “*” to show that it is installed. This will be important in the third query.

Select 
	ResourceID, 
	ApplicationName0 as 'Displayname', 
	Version0 as 'version', 
	'*'  as 'Installed' 
from 
	dbo.v_GS_WINDOWS8_APPLICATION 
where 
	ApplicationName0 like '%team%'

Query 2

This is the same as the first query but use ARP data instead.

Select 
	ResourceID, 
	DisplayName0 as 'Displayname', 
	Version0 as 'version', 
	'*' as 'Installed' 
from 
	dbo.v_Add_Remove_Programs 
where 
	DisplayName0 like '%team%' 

Query 3

Really this is the query where we are joining the first two queries together to show all devices within ConfigMgr and what version of Teams they have installed or not.

;with W8A as (Select ResourceID, ApplicationName0 as 'Displayname', Version0 as 'version', '*'  as 'Installed' from dbo.v_GS_WINDOWS8_APPLICATION where ApplicationName0 like '%team%'),
ARP as (Select ResourceID, DisplayName0 as 'Displayname', Version0 as 'version', '*' as 'Installed' from dbo.v_Add_Remove_Programs where DisplayName0 like '%team%' )

Select 
	R.netbios_name0 as 'Device Name',
	isnull(ARP.Installed, ' ') as 'ARP',
	ARP.Displayname,
	ARP.version,
	isnull(W8A.Installed, ' ') as 'W8A',
	W8A.Displayname,
	W8A.version
from 
	dbo.v_R_System R
	Left outer join ARP on ARP.ResourceID = R.ResourceID
	Left outer join W8A on W8A.ResourceID = R.ResourceID
Order by 1

As you can see here are the results for my lab. Clearly, I don’t use Teams. The final SQL query is Combine-Two-Views-into-one-Query.zip

User Installed Teams Versions

It should be noted that this will not show you any devices that have the User Installed version of Team. Why because this is not inventoried by ConfigMgr. You can read my blog over on Recast’s site for more details. User Installed Software and Why You Should Care.

Upgrade Ideas

As with anything you can make this query nicer. I would use the Isnull function to change all of the NULLs to n/a. This way everyone knows that results are expected. Next, I would add User installed versions to this list too but that requires 3^rd party add-on to collect this information.

How to Combine Two Views into one Query Video

As with many of the blogs, I’m including the steps that I took as a video.

Training Suggests

As many of you know I love both Mid-West Management Summit (MMS) and Workplace Ninja Summit, Topic like this one are covered at these events. In more details, that any blog can truly answers. Even better at these events, you can talk directly with the presenters about your particular issue. I strongly encourage you to attend these events!

If you have any questions about How to Combine Two Views into one Query please feel free to contact me @GarthMJ Please also subscribe to my YouTube channel and newsletter.