Merge SQL queries (SCCM)

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

LVL 1
amaru96Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
amaru96Author Commented:
Thanks, managed to get it mostly working.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.