Re: How to "avoid" a GROUP BY - HAVING

On Tue, 29 May 2007 17:19:00 -0700, LUIS wrote:

I have four tables,
1. First has Items,
2. Second has Features,
3. Third defines the ItemsFeatures
4. And temporary that sets which Items have to SELECT according Features
For this statment we do not need table(2),

IF (SELECT COUNT(*) FROM tblTemporary) = 0

SELECT A.ItemID, B.ItemDescrip FROM tblItems A ORDER BY 1

ELSE

SELECT A.ItemID, B.ItemDescrip
FROM tblItems A LEFT OUTER JOIN tblItemsFeatures B ON A.ItemID = B.ItemID
LEFT OUTER JOIN tblTemporary C ON B.FeatureID = C.FeatureID
GROUP BY A.ItemID, B.ItemDescrip
HAVING COUNT(B.FeatureID) = (SELECT COUNT(*) FROM tblTemporary)
ORDER BY 1

If I want all Items, first SELECT is used, if I make a Features Selection,
second SELECT is executed.
QUESTION : If there a way to use ONLY ONE SELECT to do this?

Beforehand, thank you very much

Hi LUIS,

The query below should combine these two select statements, though it
does not avoud the GROUP BY and the HAVING (I don't think you can get
the desired results without them).

SELECT A.ItemID, B.ItemDescrip
FROM tblItems A LEFT OUTER JOIN tblItemsFeatures B ON A.ItemID =
B.ItemID
LEFT OUTER JOIN tblTemporary C ON B.FeatureID =
C.FeatureID
GROUP BY A.ItemID, B.ItemDescrip
HAVING COUNT(B.FeatureID) = COALESCE(NULLIF((SELECT COUNT(*) FROM
tblTemporary),0),COUNT(B.FeatureID))
ORDER BY 1

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

BTW, do you really think that the joins need to be OUTER joins? I doubt
it, but without more information than you gave I can't be completely
sure. You might want to check if you can use INNER joins instead, since
they are often a tad faster.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
.