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…
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…