Solved

Merge SQL queries (SCCM)

Posted on 2014-11-09
2
481 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

617 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