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
Solved

Merge SQL queries (SCCM)

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

840 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