Tricky join based on results of an aggregate evaluation

In SQ开发者_运维问答L Server 2008, I have 3 tables: Definitions, Params, and Items. The fist table contains the min and max value that a Param may be. Params contains 0 or more parameters against a given definition, for an item. I wish to get the valid Item IDs by doing the appropriate conditional joins/evals.

To illustrate:

ItemID = 1 would be returned because nothing in Params violates Definitions.Min|Max.

ItemsID = 2 would not be returned because Hi = 103 violates Definitions.Max where DefID = 2. Given this, the fact the last entry violates Definitions.Low is inconsequential, but shown for clear illustration.

ItemID = 3 will match because it has a Param that is within range. There is no need to join all Definitions.

---

How about

SELECT  *
FORM    Items i INNER JOIN
        Params p    ON  i.ItemID = p.ItemID INNER JOIN
        Definitions d   ON  p.DefinationID = d.DEfinationID
WHERE   p.Hi <= d.Max
AND         p.Low >= d.Min

EDIT

Have a look at this full example

DECLARE @Items TABLE(
        ItemID INT
)
DECLARE @Params TABLE(
        DefID INT,
        ItemID INT,
        Low FLOAT,
        Hi FLOAT
)
DECLARE @Defs TABLE(
        DefID INT,
        [Min] FLOAT,
        [Max] FLOAT
)

INSERT INTO @Items SELECT 1
INSERT INTO @Items SELECT 2
INSERT INTO @Items SELECT 3

INSERT INTO @Params SELECT 1, 1, 11, 18
INSERT INTO @Params SELECT 1, 1, 13, 17
INSERT INTO @Params SELECT 2, 1, 25, 80

INSERT INTO @Params SELECT 1, 2, 12, 15
INSERT INTO @Params SELECT 1, 2, 14, 15
INSERT INTO @Params SELECT 2, 2, 50, 80
INSERT INTO @Params SELECT 2, 2, 50, 103
INSERT INTO @Params SELECT 2, 2, 10, 80

INSERT INTO @Params SELECT 2, 3, 24, 60

INSERT INTO @Defs SELECT 1, 10, 20
INSERT INTO @Defs SELECT 2, 20, 100

;WITH InvalidValues AS (
        SELECT  DISTINCT
                i.ItemID
        FROM    @Items i INNER JOIN
                @Params p   ON  i.ItemID = p.ItemID INNER JOIN
                @Defs d ON  p.DefID = d.DefID
        WHERE   p.Low < d.[Min]
        OR      p.Hi > d.[Max]
)
SELECT  *
FROM    @Items i 
WHERE   NOT EXISTS  (
                        SELECT  1
                        FROM    InvalidValues
                        WHERE   ItemID = i.ItemID
                    )
AND     EXISTS  (
                    SELECT  1
                    FROM    @Params p
                    WHERE   ItemID = i.ItemID
                )

REsult

ItemID
1
3