CODEWITHSUNDEEP
sql-servert-sql
GPGVM
GPGVM

Reputation: 5619

TSQL = ALL Predicate not returning rows

This is a schema and query of a bigger problem I am trying to figure out. I would have expected the below to return one row but it returns none. OK the eval is returning false but why???

declare @PCM TABLE
(
  ProductId int,
  CategoryId int
)
INSERT @PCM VALUES(68,158)
INSERT @PCM VALUES(68,113)
INSERT @PCM VALUES(68,138)
INSERT @PCM VALUES(68,161)
INSERT @PCM VALUES(70,158)
INSERT @PCM VALUES(70,273)

declare @ProdFltr TABLE
(
 CategoryId int
)
INSERT @ProdFltr VALUES(158)
INSERT @ProdFltr VALUES(113)

SELECT P.ProductId FROM @PCM P WHERE P.CategoryId = ALL(SELECT CategoryId FROM @ProdFltr)

OK I think I was mis-reading the MSDN docs as this works exactly as I would expect. Use this instead of the above for the ProdFltr

declare @ProdFltr TABLE
(
 CategoryId int,
 CategoryId2 int
)

INSERT @ProdFltr VALUES(158,113)

So the ALL can only deal with one row from the subquery??

@acfrancis and Amirreza Keshavarz your answers filled in the gap thank you VERY much. So how would I get JUST the pcm records that matched EVERYTHING in the ProdFltr?

Upvotes: 0

Views: 98

Answers (2)

acfrancis
acfrancis

Reputation: 3661

ALL will always be false in your scenario because for each individual row of @PCM, the CategoryId column can't be equal to all the CategoryId values (all the rows) in @ProdFltr. The only way it would be true is if @ProdFltr had all of its rows with the same value for CategoryId.

EDIT: With the new information in the comments, I think you want a select like this:

SELECT P.ProductId 
FROM @PCM P 
WHERE NOT EXISTS (SELECT *
                  FROM @ProdFltr PF
                  WHERE NOT EXISTS (SELECT *
                                    FROM @PCM P2
                                    WHERE P2.ProductId = P.ProductId
                                          AND P2.CategoryId = PF.CategoryId))

In other words, select the ProductId if there isn't a row in @ProdFltr that isn't matched by a row in @PCM for the same ProductId.

You might still add a DISTINCT on the outermost select if you don't want repeated ProductIds.

Upvotes: 1

Amir Keshavarz
Amir Keshavarz

Reputation: 3108

ALL requires the scalar_expression to compare positively to every value that is returned by the subquery. For instance, if the subquery returns values of 2 and 3, scalar_expression <= ALL (subquery) would evaluate as TRUE for a scalar_expression of 2. If the subquery returns values of 2 and 3, scalar_expression = ALL (subquery) would evaluate as FALSE, because some of the values of the subquery (the value of 3) would not meet the criteria of the expression. For statements that require the scalar_expression to compare positively to only one value that is returned by the subquery, see SOME | ANY (Transact-SQL).

From : Msdn

Upvotes: 1

Related Questions