Databases
StackOverflow2013
Postsdbo
POAll rows where at least one child has all of its own children pass a condition
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAll rows where at least one child has all of its own children pass a condition
    text
    copied!<p>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.</p> <p><strong>The Problem</strong></p> <p>Let's say I have some tables:</p> <pre><code>Prize - PrizeId RulePrize_Map - PrizeId - RuleId Rule - RuleId Conditional - ConditionalId - RuleId - InputId - ExpectedValue (bit) Input - InputId </code></pre> <p>A <code>Prize</code> is won when at least one <code>Rule</code> is true. A <code>Rule</code> is true when <em>all</em> of its <code>Conditionals</code> are true. A <code>Conditional</code> is "true" when its <code>InputId</code> is either present or not present in the <code>Input</code> table, as designated by the <code>ExpectedValue</code> field. This could perhaps be treated as equivalent to: <code>Count(InputId in Input table)</code> = <code>ExpectedValue</code> for the <code>Conditional's</code> <code>InputId</code>. </p> <p>Some Examples:</p> <pre><code>Conditional (InputId = 11, ExpectedValue = 1) -&gt; True if InputId 11 in Input Table Conditional (InputId = 12, ExpectedValue = 0) -&gt; True if Inputid 12 NOT in Input Table </code></pre> <p><strong>My Goal</strong></p> <p>I want to get all <code>Prizes</code> where at least one <code>Rule</code> is "true". I would settle for: "All <code>Rules</code> that are true".</p> <p><strong>My Attempt</strong></p> <pre><code>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 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload