Solved

Merge SQL queries (SCCM)

Posted on 2014-11-09
2
440 Views
Last Modified: 2014-11-10
Hi guys, I have 2 SQL queries which I need to merge.

The first query gives me the collections and their members, where the second query gives me all the members along with their details. How do I merge the 2 queries so I can get the list of collections along with their members (first query) to also include the details from the second (ie. OS, service pack, model, IP....)?

Query1

SELECT 
v_FullCollectionMembership.CollectionID AS 'CollID', 
v_Collection.Name AS 'CollName',
v_FullCollectionMembership.Name AS 'SystemName'
FROM
v_FullCollectionMembership, v_Collection
WHERE v_FullCollectionMembership.CollectionID = v_Collection.CollectionID
ORDER BY
CollName ASC, SystemName ASC

Open in new window


Query 2
SELECT    
 dbo.v_GS_COMPUTER_SYSTEM.Name0 AS [NetBIOS Name],  
 dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Make], 
 dbo.v_GS_COMPUTER_SYSTEM.Model0 AS [Model],  
 dbo.v_RA_System_IPAddresses.IP_Addresses0 AS [IP Address],
  v_GS_OPERATING_SYSTEM.Caption0 AS [OS Type],
  v_GS_OPERATING_SYSTEM.CSDVersion0 AS [OS Version], 
 --dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Service Tag (Dell)] 
 v_GS_PC_BIOS.SerialNumber0 AS [Serial]
  
FROM         dbo.v_GS_COMPUTER_SYSTEM 
   
 INNER JOIN   
  dbo.v_GS_PC_BIOS  
  ON  
  dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_GS_PC_BIOS.ResourceID  
 INNER JOIN  
  dbo.v_RA_System_IPAddresses  
  ON 
  dbo.v_GS_PC_BIOS.ResourceID = dbo.v_RA_System_IPAddresses.ResourceID  
 INNER JOIN 
  dbo.v_GS_SYSTEM_ENCLOSURE  
  ON 
  dbo.v_RA_System_IPAddresses.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID  
 INNER JOIN 
  dbo.v_GS_OPERATING_SYSTEM  
  ON 
  dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID  
 WHERE     
  (dbo.v_RA_System_IPAddresses.IP_Addresses0 LIKE '10.%') 
  AND 
  (Roles0 like '%Server_NT%') 
ORDER BY 
  [NetBIOS Name]  

Open in new window

0
Comment
Question by:amaru96
2 Comments
 
LVL 7

Accepted Solution

by:
slubek earned 500 total points
ID: 40431847
I don't know if I understand you correctly, but what is your two queries' merging field?
If [NetBIOS name] then try something like
select * from (Select v_FullCollectionMembership.CollectionID AS 'CollID', ... ) q1 
INNER JOIN (dbo.v_GS_COMPUTER_SYSTEM.Name0 AS [NetBIOS Name], ...) q2 
on q1.SystemName=q2.[NetBIOS Name]

Open in new window

You can also create view v_q1 as query1 and v_q2 as query2 and join them - the code will be more understandable.
0
 
LVL 1

Author Comment

by:amaru96
ID: 40432235
Thanks, managed to get it mostly working.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now