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.