SQL - Counting through a returned dataset

[RogueSuit]

I have the following query:
Select 'TEST' AS CatName, f.ProductID AS ParentSku, f.Name AS PrntSkuNm, t.ProductID AS ChildSku, t.Name AS ChlSkuNm, t.DisplayInd, t.oid AS ChildID, f.oid AS ParentID, getdate() AS DateGenerated, a.Name

FROM [TEST_CatalogRelationships] a INNER JOIN
[TEST_CatalogProducts] f ON a.from_oid = f.oid INNER JOIN
[TEST_CatalogProducts] t ON a.to_oid = t.oid

Where (a.Name like 'CROSS %')
ORDER BY ParentSku

It returns something like:

CatName ParentSku PrntSkuNm ChildSku ChlSkuNm DisplayInd DateGenerated TypeName
CAT 43-2009 testDesc 23-125 childDesc Yes date/time CROSS
CAT 43-2009 testDesc 23-126 childDesc Yes date/time CROSS
CAT 43-2009 testDesc 23-127 childDesc Yes date/time CROSS
CAT 53-2300 testDesc 23-568 childDesc No date/time CROSS
CAT 53-2300 testDesc 23-567 childDesc Yes date/time CROSS
CAT 53-2300 testDesc 23-520 childDesc Yes date/time CROSS
CAT 62-5600 testDesc 23-125 childDesc Yes date/time CROSS
CAT 62-5600 testDesc 23-129 childDesc Yes date/time CROSS
CAT 62-5600 testDesc 23-234 childDesc Yes date/time CROSS
CAT 87-2320 testDesc 23-125 childDesc Yes date/time CROSS

My goal is to only show the rows where there are less than 3 ChildSkus associated to the ParentSku and if DisplayInd = yes count that as an association and if DisplyInd = No than that counts as one less association. With the data above after goal conditions are implemented – it should return something like:

CatName ParentSku PrntSkuNm ChildSku ChlSkuNm DisplayInd DateGenerated TypeName
CAT 53-2300 testDesc 23-568 childDesc No date/time CROSS
CAT 53-2300 testDesc 23-567 childDesc Yes date/time CROSS
CAT 53-2300 testDesc 23-520 childDesc Yes date/time CROSS
CAT 87-2320 testDesc 23-125 childDesc Yes date/time CROSS

I tried to count through the set and then from the retuned value only display those rows where count is < 3 and DisplayInd = No, but I could not get the Query to operate properly.

Any Suggestions would be appreciated…

 

[djohns]

You can use a subquery along with this that groups by ParentSku and counts ChildSku 's and selects only those ParentSku's HAVING counts of ChildSku's <=3.