SQL Query Help

[cfreeman21]

I am trying to get a collection and then member count for that collection and if referenced collections list those and give me those counts... I don't seem to be able to get the dependent collection count any sql gurus able to assist?

SELECT 
  c.Name AS [Collection Name],
  c.Comment AS [Collection Comment],
  c.CollectionID AS [Collection ID],
  c. [Membercount],
  cd.SourceCollectionID AS 'Collection Dependency',
  cc.name as CollectionDependencyName,
  CASE
    WHEN cd.relationshiptype = 1 THEN 'Limited To ' + c.name + ' (' + cd.SourceCollectionID + ')'
    WHEN cd.relationshiptype = 2 THEN 'Include ' + c.name + ' (' + cd.SourceCollectionID + ')'
    WHEN cd.relationshiptype = 3 THEN 'Exclude ' + c.name + ' (' + cd.SourceCollectionID + ')'
  END AS 'Type of Relationship',
COUNT (cd.DependentcollectionID) AS [Dependent Count]
FROM v_Collection c
JOIN  v_FullCollectionMembership fc ON  c.CollectionID = fc.CollectionID
JOIN vSMS_CollectionDependencies cd ON c.CollectionID = cd.DependentCollectionID
JOIN v_Collection cc ON cc.CollectionID = cd.SourceCollectionID
WHERE c.CollectionID = 'A1200601'
GROUP BY c.Name,
         c.Comment,
         c.CollectionID,
              c.Membercount,
              cd.SourceCollectionID,
              cd.RelationshipType,
              cc.name
ORDER BY c.Name

[Peter33]

Note sure what youre trying to do. But i think i figured it out by reading your query.

SELECT 
  c.Name AS [Collection Name],
  c.Comment AS [Collection Comment],
  c.CollectionID AS [Collection ID],
  c. [Membercount],
  cd.SourceCollectionID AS 'Collection Dependency',
  cc.name as CollectionDependencyName,
  CASE
    WHEN cd.relationshiptype = 1 THEN 'Limited To ' + c.name + ' (' + cd.SourceCollectionID + ')'
    WHEN cd.relationshiptype = 2 THEN 'Include ' + c.name + ' (' + cd.SourceCollectionID + ')'
    WHEN cd.relationshiptype = 3 THEN 'Exclude ' + c.name + ' (' + cd.SourceCollectionID + ')'
  END AS 'Type of Relationship',
  (
	SELECT a. [Membercount] FROM  v_Collection a WHERE a.CollectionID = cd.SourceCollectionID
  ) AS [Dependent Count]
FROM v_Collection c
JOIN  v_FullCollectionMembership fc ON  c.CollectionID = fc.CollectionID
JOIN vSMS_CollectionDependencies cd ON c.CollectionID = cd.DependentCollectionID
JOIN v_Collection cc ON cc.CollectionID = cd.SourceCollectionID
WHERE c.CollectionID = 'A1200601'
GROUP BY c.Name,
         c.Comment,
         c.CollectionID,
              c.Membercount,
              cd.SourceCollectionID,
              cd.RelationshipType,
              cc.name
ORDER BY c.Name

[cfreeman21]

Yes Peter33, That is exactly what I wanted. Thanks!