All rows in which at least one child has all their own children convey the condition

Question

I have a little problem with the SQL query, and I thought that I would ask the wisdom of the crowd to see what I did not see. I'm sure the below works, but it seems very bad, and I wonder if there is a more reasonable way (ideally, using joins instead of subselects).

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 won when at least one Rule is true. A Rule true when all of its Conditionals are true. A Conditional is “true” when its InputId present or absent in the Input table, as indicated in the ExpectedValue field. This could be considered equivalent: Count(InputId in Input table) = ExpectedValue for 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 agree: "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 

Answer 1

The question will change a bit, so I redid 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 

Answer 2

To get all RuleId where ALL RuleId 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 is perhaps slower, but this does not prevent you from checking the speed. It does not use CASE , but the difference ( EXCEPT ) of two JOIN s, only one of them uses 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 

Answer 3

Try the following:

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