August 11, 2010 at 1:53 am
Hi,
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 can be found at Rapidshare Or, from lsoon3@gmail.com
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:
===============
NoAsset TagBrandModelComputer NameUserOSOffice
===========================================================================================
1AABBHPSSMITDW0178User1XP ProOffice 2003
2BBCCHPHPDC5800 SSMITDW0173User2XP ProOffice 2003 Pro
3CCDDHPDC5700SSMITDW0191User3XP ProOffice 2007 En
4EEHHHPDC5700SSMITDW0082User4XP ProOffice 2007 Pro
August 12, 2010 at 5:54 am
Why do you actually want to join these 2 queries?
August 12, 2010 at 1:39 pm
Because of all the heinous joins and unreadability in the second query, I'd recommend taking both query results and throwing them into session temp tables first. It'll make your final SQL Statement to join the two a lot easier to deal with.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply