Jump to content


MastaMOFO

Established Members
  • Posts

    1
  • Joined

  • Last visited

Everything posted by MastaMOFO

  1. 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.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.