Need some T-SQl help

[Keiichi]

I'm running into a roadblock here I have a query which is this:

SELECT     ItemCode, MAX(Quantity) AS Expr1
FROM         ItemDefaultPrice
WHERE     (Quantity > 1)
GROUP BY ItemCode
ORDER BY ItemCode

That properly returns records of the highest value of the item that has multiple quantity tiers.

When I plug it in as a subquery:

SELECT     CID, ItemCode, Quantity, Price, Bonus, Multiplier, AdditionalSH, VolumeTier
FROM         ItemDefaultPrice
WHERE     EXISTS
                          (SELECT     ItemCode, MAX(Quantity) AS Expr1
                            FROM          ItemDefaultPrice AS ItemDefaultPrice_1
                            WHERE      (Quantity > 1)
                            GROUP BY ItemCode)
ORDER BY ItemCode

It returns all records in that table. am I missing something here?

 

[FreiDOg]

You don't have any additional constraint on the sub select to tie it to a record in the main select. Since there exists at least one ItemCode in the table that appears more than once, the exists always returns true.

A good way to link the ItemCodes would be to select a list if ItemCodes that appear more than once and make it a condition of the main select its ItemCode is in that list.

 

[mikeblas]

What you're missing, I guess, is an understanding of how the EXISTS operator works. Considering EXISTS(A), the operator evaluates TRUE if there are any rows from the subquery A. Since there are rows, your outer WHERE clause always evaluates TRUE and then gives you all the rows from the outer reference to ItemDefaultPrice. EXISTS() implies no correlation between the inner query and the outer query; and you have explicitly made no such correlation.

It's hard to suggest a correct query without knowing what you intend to query, or what your data model is. It's hard to provide more help than this for now, then.