Using the "HAVING" clause as a Filter

[RogueSuit]

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.

 

[IonelBurtan]

Try to modify your GROUP BY clause in

GROUP BY t.oid, f.oid

or

GROUP BY f.oid, t.oid

and let me know what happens.

Hope this helps,s-) Blessed is he who in the name of justice and good will, shepards the week through the valley of darknees...

http://home.domaindlx.com/ionelb

 

[RogueSuit]

I got some interesting results:

First, the message window in the query analyzer would display this -
Column '[THE COLUMN NAME]' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
...........
[THE COLUMN NAME] = all of the objects in the Select statement.

So I added a name to GROUP BY and ran the PROC. Each time it would give me the message above with the name of the next item in the select statement. I got the message to go away be doing the following:
GROUP BY f.oid, t.oid, f.productID, f.name, t.displayInd, t.ProductID, t.name

Basically defeating the purpose of the statement - the query returned all data, unfiltered.

Any ideas? Thanks for your help…