I have the following query:
Select 'CATALOG' AS CatName, f.ProductID AS ParentSku, f.Name AS ParentSkuName, t.ProductID AS ChildSku, t.Name AS ChildSkuName, t.DisplayInd, t.oid AS ChildID, f.oid AS ParentID, getdate() AS DateGenerated, a.Name
FROM [CATALOG_CatalogRelationships] a INNER JOIN
[CATALOG_CatalogProducts] f ON a.from_oid = f.oid INNER JOIN
[CATALOG_CatalogProducts] t ON a.to_oid = t.oid
Where (a.Name like 'CROSS_SELL%') AND (t.DisplayInd like 'YES')
GROUP BY f.oid
HAVING Count(t.oid) < 3
f.oid has a one to many relationship with t.oid. I am trying to count the number of instances t.oid happens. So if you have 2 instances of t.oid to a single f.oid the t.oid count would be 2 and the row would appear in the output.
Currently, the count is always one (I assume this is because it is only counting each row) and it displays all of the data as if the query was ignoring the “HAVING” clause.
Please forgive the syntax – if I am doing something wrong or there is a better way to do what I am trying to do; I would appreciate the advice.
Select 'CATALOG' AS CatName, f.ProductID AS ParentSku, f.Name AS ParentSkuName, t.ProductID AS ChildSku, t.Name AS ChildSkuName, t.DisplayInd, t.oid AS ChildID, f.oid AS ParentID, getdate() AS DateGenerated, a.Name
FROM [CATALOG_CatalogRelationships] a INNER JOIN
[CATALOG_CatalogProducts] f ON a.from_oid = f.oid INNER JOIN
[CATALOG_CatalogProducts] t ON a.to_oid = t.oid
Where (a.Name like 'CROSS_SELL%') AND (t.DisplayInd like 'YES')
GROUP BY f.oid
HAVING Count(t.oid) < 3
f.oid has a one to many relationship with t.oid. I am trying to count the number of instances t.oid happens. So if you have 2 instances of t.oid to a single f.oid the t.oid count would be 2 and the row would appear in the output.
Currently, the count is always one (I assume this is because it is only counting each row) and it displays all of the data as if the query was ignoring the “HAVING” clause.
Please forgive the syntax – if I am doing something wrong or there is a better way to do what I am trying to do; I would appreciate the advice.