Video Screencast Help
Search Video Help Close Back
to help

Report - join sql querys

Created: 17 Jan 2013 | Updated: 22 Jan 2013 | 2 comments
cathossie's picture
cathossie
0 0 Votes
Login to vote
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

 

Discussion Filed Under:
, , , , , ,

Comments 2 Comments • Jump to latest comment

mclemson's picture
mclemson Trusted Advisor Partner Accredited
Report - join sql querys - Comment:17 Jan 2013 : Link

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

0
Login to vote
  • Actions
cathossie's picture
cathossie
Report - join sql querys - Comment:21 Jan 2013 : Link

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
0
Login to vote
  • Actions