0

I am new to SQL query. I would like to join two SQL queries (MS Office Query and Computer Details Quer) into one.
The tables output can be found at http://rapidshare.com/files/412276985/SQL_query.zip
Or, from [email removed]

Thanks for your guidance, below are the query:


1) MS Office Query:
================


select v_Add_Remove_Programs.DisplayName0 AS [MS Office Edition] from v_Add_Remove_Programs where DisplayName0 like 'Microsoft Office Project Professional%'
OR DisplayName0 like 'Microsoft Office Visio Standard%' OR DisplayName0 like'Microsoft Office Visio Professional%' OR DisplayName0 like 'Microsoft Office Enterprise%'OR DisplayName0 like 'Microsoft Office Standard%' OR DisplayName0 like 'Microsoft Office Professional%'

2) Computer Details Query:
=======================

select  distinct 
 v_R_System.ResourceID, 
 v_R_System.Netbios_Name0 AS [Computer Name], 
 v_R_System.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], 
 v_Site.SiteName as [SMS Site Name], 
 [Top Console User] = CASE 
 when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1') 
 then 'Unknown' 
 Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 
 End, 
 v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], 
 v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level], 
 v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number], 
 v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag], 
 v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer], 
 v_GS_COMPUTER_SYSTEM.Model0 AS [Model], 
 v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)], 
 v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)], 
 (Select sum(Size0) 
 from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) 
  where v_GS_LOGICAL_DISK.ResourceID =v_R_System.ResourceID and 
  v_FullCollectionMembership.CollectionID = 'PS100010') As [Disk Space (MB)], 
 (Select sum(v_GS_LOGICAL_DISK.FreeSpace0) 
 from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) 
 where v_GS_LOGICAL_DISK.ResourceID =v_R_System.ResourceID and v_FullCollectionMembership.CollectionID = 'PS100010') As [Free Disk Space (MB)] 
 from v_R_System 
 inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID) 
 left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System.ResourceID) 
 inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID) 
 inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System.ResourceID) 
 inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System.ResourceID) 
 inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System.ResourceID) 
 left  join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode) 
 inner join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2) 
 left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System.ResourceID) 
 Where v_FullCollectionMembership.CollectionID = 'PS100010' 
 Order by v_R_System.Netbios_Name0

Expected Output:
===============

No Asset Tag Brand Model Computer Name User OS Office
===========================================================================================
1 AABB HP SSMITDW0178 User1 XP Pro Office 2003
2 BBCC HP HPDC5800 SSMITDW0173 User2 XP Pro Office 2003 Pro
3 CCDD HP DC5700 SSMITDW0191 User3 XP Pro Office 2007 En
4 EEHH HP DC5700 SSMITDW0082 User4 XP Pro Office 2007 Pro

Edited by Ezzaral: Added code tags. Please use them to format any code that you post.

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by tesuji
0

Hi

These tables can hardly be joined together for the 2nd query does not contain table v_Add_Remove_Programs of 1st query. Possibly the program what auto-generated the second query could know how v_Add_Remove_Programs is related to the tables v_GS_LOGICAL_DISK, v_FullCollectionMembership, v_R_System etc. of 2nd query. One also need to know where to find column DisplayName0 in those tables.

-- tesu

Edited by tesuji: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.