SELECT *
FROM Product p
INNER JOIN Threshold t ON p.ProductId = t.ProductId
WHERE (Operator = '>' AND p.Quantity > ThresholdValue)
OR (Operator = '<' AND p.Quantity < ThresholdValue)
SELECT *,
IsOverThreashold = CASE
WHEN (
Operator = '>'
AND p.Quantity > ThresholdValue
)
OR (
Operator = '<'
AND p.Quantity < ThresholdValue
)
THEN 1
ELSE 0
END
FROM Product p
INNER JOIN Threshold t
ON p.ProductId = t.ProductId
The user want in the Threshold table a "Version" field
for the ThresholdValue, so, in the matching, I have to
use the latest version (i.e. with the highest version number).
SELECT *
FROM Product p
INNER JOIN Threshold t ON p.ProductId = t.ProductId
WHERE (Operator = '>' AND p.Quantity > ThresholdValue)
OR (Operator = '<' AND p.Quantity < ThresholdValue)
SELECT *
FROM Product p
CROSS APPLY
(
SELECT TOP 1 *
FROM Threshold x
WHERE x.ProductId = p.ProductId
ORDER BY Version DESC
) t
WHERE (t.Operator = '>' AND p.Quantity > t.ThresholdValue)
OR (t.Operator = '<' AND p.Quantity < t.ThresholdValue)