Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

Report - join sql querys

Created: 17 Jan 2013 • Updated: 22 Jan 2013 | 2 comments
This issue has been solved. See solution.

Hi, I am just learning sql I have the following 2 querys that I want to combine, they both give part of the results I need but in separate reports

The brief from the client is to create a report on the following for a future outlook rollout we will be doing:

  • Computer Name
  • User
  • MOEversion (this is a dataclass we've created) 
  • Office version
  • IE version
  • Managed/Unmanaged

first report - which I have created with the query builder but can convert to sql

DECLARE @v1_TrusteeScope nvarchar(max)
   SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT
   [vri2_Computer].[Guid] AS [_ItemGuid],
   [vri2_Computer].[Name],
   [dca4_MOEVersion].[MOEVersion],
   [dca3_AeX AC Identification].[Last Logon User],
   [vri2_Computer].[IsManaged]
FROM
   [vRM_Computer_Item] AS [vri2_Computer]
      LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca3_AeX AC Identification]
         ON ([vri2_Computer].[Guid] = [dca3_AeX AC Identification].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_MOEVersion] AS [dca4_MOEVersion]
         ON ([vri2_Computer].[Guid] = [dca4_MOEVersion].[_ResourceGuid])
      INNER JOIN [Inv_AeX_AC_Client_Agent] AS [ajs5_Inv_AeX_AC_Client_Agent]
         ON ([vri2_Computer].[Guid] = [ajs5_Inv_AeX_AC_Client_Agent].[_ResourceGuid])
WHERE
   (
      ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
   )
GROUP BY
   [vri2_Computer].[Guid],
   [dca4_MOEVersion].[MOEVersion],
   [vri2_Computer].[Name],
   [vri2_Computer].[IsManaged],
   [dca3_AeX AC Identification].[Last Logon User]
ORDER BY
   [dca4_MOEVersion].[MOEVersion] DESC
 

Second report - found the sql on the forums

SELECT vc.Guid,vc.Name, arp.DisplayName,arp.DisplayVersion

FROM vComputer vc

JOIN Inv_AddRemoveProgram arp ON arp._ResourceGuid=vc.Guid

WHERE DisplayName LIKE 'microsoft office professional %' AND arp.InstallFlag=1

Comments 2 CommentsJump to latest comment

mclemson's picture

Try this:

DECLARE @v1_TrusteeScope nvarchar(max)
   SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT
   [vri2_Computer].[Guid] AS [_ItemGuid],
   [vri2_Computer].[Name],
   arp.DisplayName as 'Office Name',
   arp.DisplayVersion as 'Office Version',
   [dca4_MOEVersion].[MOEVersion] as 'MOE Version',
   [dca3_AeX AC Identification].[Last Logon User],
   [vri2_Computer].[IsManaged]
FROM
   [vRM_Computer_Item] AS [vri2_Computer]
      LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca3_AeX AC Identification]
         ON ([vri2_Computer].[Guid] = [dca3_AeX AC Identification].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_MOEVersion] AS [dca4_MOEVersion]
         ON ([vri2_Computer].[Guid] = [dca4_MOEVersion].[_ResourceGuid])
      INNER JOIN [Inv_AeX_AC_Client_Agent] AS [ajs5_Inv_AeX_AC_Client_Agent]
         ON ([vri2_Computer].[Guid] = [ajs5_Inv_AeX_AC_Client_Agent].[_ResourceGuid])
      JOIN Inv_AddRemoveProgram arp ON arp._ResourceGuid=vri2_Computer.Guid
WHERE
   (
      ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
 AND
 arp.DisplayName LIKE 'Microsoft Office Professional%' AND arp.InstallFlag=1
   )
GROUP BY
   [vri2_Computer].[Guid],
   [dca4_MOEVersion].[MOEVersion],
   [vri2_Computer].[Name],
   [vri2_Computer].[IsManaged],
   [dca3_AeX AC Identification].[Last Logon User]
ORDER BY
   [dca4_MOEVersion].[MOEVersion] DESC
 
 
Also, compare your first query with my posted query at a site like textdiff.com to learn how I combined them.

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner
intuitivetech.com

cathossie's picture

Hi Mclemson

I tried your script but it was not runnable but the tip about textdiff.com is very useful for the future, I found the following vidie which helped me use the report maker to create the report.

http://www.symantec.com/connect/videos/symantec-management-platform-reports-creating-dropdown-menu-filter

The resolved query is as follows

DECLARE @v1_TrusteeScope nvarchar(311)
   SET @v1_TrusteeScope = N'{2E1F478A-4986-4223-9D1E-B5920A63AB41},{34DE786C-E6D1-49CC-AB40-E612D9A2094F},{44EB5117-EE86-4BD5-89BE-315A4455ED41},{511BD57A-8F96-4D94-B490-4DBC85384AD7},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F},{B7C587DF-6710-4FB6-A1A2-1864CD9E69B7},{BAE37EAA-E122-47D9-9EA7-77F7C27D748B}'
DECLARE @v2_MicrosoftOfficeProfessional nvarchar(1)
   SET @v2_MicrosoftOfficeProfessional = N'%'
SELECT
   [vri3_Computer].[Guid] AS [_ItemGuid],
   [vri3_Computer].[Name],
   [dca6_MOEVersion].[MOEVersion],
   [dca5_AeX AC Identification].[Last Logon User],
   [dca4_AddRemoveProgram].[DisplayName],
   [vri3_Computer].[IsManaged]
FROM
   [vRM_Computer_Item] AS [vri3_Computer]
      LEFT OUTER JOIN [Inv_AddRemoveProgram] AS [dca4_AddRemoveProgram]
         ON ([vri3_Computer].[Guid] = [dca4_AddRemoveProgram].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca5_AeX AC Identification]
         ON ([vri3_Computer].[Guid] = [dca5_AeX AC Identification].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_MOEVersion] AS [dca6_MOEVersion]
         ON ([vri3_Computer].[Guid] = [dca6_MOEVersion].[_ResourceGuid])
      INNER JOIN [Inv_AeX_AC_Client_Agent] AS [ajs7_Inv_AeX_AC_Client_Agent]
         ON ([vri3_Computer].[Guid] = [ajs7_Inv_AeX_AC_Client_Agent].[_ResourceGuid])
WHERE
   (
      (
         ([dca4_AddRemoveProgram].[DisplayName] LIKE @v2_MicrosoftOfficeProfessional)
      )
      AND
      ([vri3_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
   )
GROUP BY
   [vri3_Computer].[Guid],
   [dca6_MOEVersion].[MOEVersion],
   [vri3_Computer].[Name],
   [vri3_Computer].[IsManaged],
   [dca5_AeX AC Identification].[Last Logon User],
   [dca4_AddRemoveProgram].[DisplayName]
ORDER BY
   [dca6_MOEVersion].[MOEVersion] DESC
 

SOLUTION