Joining complex SQL tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Joining complex SQL tables

Author

Topic

lsoon3
Starting Member

1 Post

Posted - 2010-08-11 : 03:33:18

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 output can be found at http://rapidshare.com/files/412278132/SQL_query.zip

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:
===============

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

Thank you
-Kevin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-11 : 10:26:10

what are common columns you've to act as link between the queries?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources