All rows where at least one child has all of its own children pass a condition

I'm having a little trouble with a SQL query, and thought I'd solicit the wisdom of the crowd to see what I'm missing. I'm pretty sure the below works, but it seems really poor and I'm wondering if there's a smarter way (ideally using joins instead of sub-selects) to do this.

The Problem

Let's say I have some tables:

Prize
  - PrizeId

RulePrize_Map
  - PrizeId
  - RuleId

Rule
  - RuleId

Conditional
  - ConditionalId
  - RuleId
  - InputId
  - ExpectedValue (bit)

Input
  - InputId

A Prize is won when at least one Rule is true. A Rule is true when all of its Conditionals are true. A Conditional is "true" when its InputId is either present or not present in the Input table, as designated by the ExpectedValue field. This could perhaps be treated as equivalent to: Count(InputId in Input table) = ExpectedValue for the Conditional's InputId.

Some Examples:

Conditional (InputId = 11, ExpectedValue = 1) -> True if InputId 11 in Input Table
Conditional (InputId = 12, ExpectedValue = 0) -> True if Inputid 12 NOT in Input Table

My Goal

I want to get all Prizes where at least one Rule is "true". I would settle for: "All Rules that are true".

My Attempt

开发者_运维知识库select p.PrizeId from Prize p INNER JOIN RulePrize_Map rpm ON rpm.PrizeId = p.PrizeId
WHERE p.PrizeId IN
(select r.PrizeId from Rule r
where
  (select count(*) from Conditional c1 where c1.RuleId = r.RuleId)
  = 
  (select count(*) from Conditional c2
   where c2.RuleId = r.RuleId AND
   (select count(*) from Input i where i.InputId = c2.InputId) = c2.ExpectedValue
  )
)
GROUP BY p.prizeId

The question change a bit, so I have redone the answer...

SELECT
  PrizeId
FROM
  (
  SELECT
    PrizeRule_Map.PrizeId,
    PrizeRule_Map.RuleId
  FROM
    PrizeRule_Map
  INNER JOIN
    Rule
      ON Rule.RuleId = PrizeRule_Map.RuleId
  INNER JOIN
    Conditional
      ON Conditional.RuleId = Rule.RuleID
  LEFT JOIN
    Input
      ON Input.InputId = Conditional.InputID
  GROUP BY
    PrizeRule_Map.PrizeId,
    PrizeRule_Map.RuleId
  HAVING
    COUNT(*) = SUM(CASE Conditional.ExpectedValue
                     WHEN 1 THEN CASE WHEN Input.InputId IS NULL THEN 0 ELSE 1 END
                     WHEN 0 THEN CASE WHEN Input.InputId IS NULL THEN 1 ELSE 0 END
                   END
                   )
  )
    AS map
GROUP BY
  PrizeId

To get all RuleIds where ALL Conditionals are true:

SELECT r.RuleID
FROM Rule r
  JOIN Conditional c
    ON c.RuleId = r.RuleId
  LEFT JOIN Input i
    ON i.InputId = c.InputId
GROUP BY r.RuleID
HAVING COUNT( CASE WHEN (c.ExpectedValue=1) AND (i.InputId IS NOT NULL)
                     OR (c.ExpectedValue=0) AND (i.InputId IS NULL)
                   THEN 1
                   ELSE NULL 
              END ) 
       = COUNT( * )

Another way - probably slower but it doesn't hurt to test for speed. It doesn't use CASE but the difference (EXCEPT) of two JOINs, only one of them using GROUP BY:

    SELECT r.RuleID
    FROM Rule r
      JOIN Conditional c
        ON c.RuleId = r.RuleId
      LEFT JOIN Input i
        ON i.InputId = c.InputId
    WHERE c.ExpectedValue = 1
    GROUP BY r.RuleID
    HAVING COUNT( i.InputId ) = COUNT( * )
  EXCEPT
    SELECT r.RuleID
    FROM Rule r
      JOIN Conditional c
        ON c.RuleId = r.RuleId
      JOIN Input i
        ON i.InputId = c.InputId
    WHERE c.ExpectedValue = 0

Try the following:

SELECT Rule.RuleId, Rule.RuleName
FROM Rule
INNER JOIN Conditional ON Rule.RuleId = Conditional.RuleId
Where Conditional.ExpectedValue == true