Jump to content


Sign in to follow this  
MastaMOFO

Query Referenced Collections

Recommended Posts

I am working on several SQL queries that will return a list of referenced collections names of a top level collection. I am able to query the top level collection name along with the refrenced collections but I can't seem to seperate the two. I'm wanting to import this into Report Builder and have sub-selects. For instance, I have 8 top level collections. One of the collection names is 'XXX WRK_NA' it has 24 referenced collections. I want to be able to click the '+' for the subselect and it would reveal the 24 referenced collections in a list. I have included two queries below:

 

Top Level Collection Query:

 

SELECT

 

DISTINCT

 

COUNT(v_R_System.Operating_System_Name_and0)as 'Windows 7',v_Collection.Name,(v_Collection.MemberCount) as 'Number of Workstations'

FROM

 

v_R_System INNER JOIN

v_FullCollectionMembership_Valid

ON v_R_System.ResourceID = v_FullCollectionMembership_Valid.ResourceID INNER JOIN

v_Collection

ON v_FullCollectionMembership_Valid.CollectionID = v_Collection.CollectionID

WHERE

 

(v_Collection.Name LIKE 'XXXX WRK[_]%')AND (v_R_System.Operating_System_Name_and0 like 'Microsoft Windows NT Workstation 6.1')

 

GROUP

 

BY v_Collection.Name, v_Collection.MemberCount, v_R_System.Operating_System_Name_and0

 

Subselect Query:

 

SELECT

 

DISTINCT

 

COUNT(v_R_System.Operating_System_Name_and0)as 'Windows 7',(v_Collection.Name) AS 'Site',(v_Collection.MemberCount) as 'Number of Workstations'

 

FROM

 

v_R_System INNER JOIN

v_FullCollectionMembership_Valid

ON v_R_System.ResourceID = v_FullCollectionMembership_Valid.ResourceID INNER JOIN

v_Collection

ON v_FullCollectionMembership_Valid.CollectionID = v_Collection.CollectionID

WHERE

 

(v_Collection.Name LIKE 'XXXX WRK%')AND(v_Collection.Name NOT LIKE 'XXXX WRK[_]%')

 

AND(v_R_System.Operating_System_Name_and0 like 'Microsoft Windows NT Workstation 6.1')

GROUP

 

BY v_Collection.Name, v_Collection.MemberCount, v_R_System.Operating_System_Name_and0

 

Both Queries work, but I'm trying to associate the list of queried collections from the subselect with each collection names in the top level collection.

 

Share this post


Link to post
Share on other sites


Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...