Solved

Merge SQL queries (SCCM)

Posted on 2014-11-09
2
459 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

740 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